Change ownership of Table

Hi All,

I have a shared SQL with a remote host ISP. I created all the tables with a script but a few of them I did not stipulate 'dbo.CreateProcedure' so it created the table with my login as the owner or schema. Those specific tables now need to be accessed via their name as they are not dbo.xzcxz, What is the correct syntax for a procedure to change their schema all to dbo.

For example I want to change

rtf375.Tbl_Mystuff  

to

dbo.Tbl_Mystuff

I hope I have explained it fully.

Best regards

Abiel M de Groot Sanders

PS. I will be out of the office tomorrow.
LVL 5
Abiel de GrootDeveloperAsked:
Who is Participating?
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Eep... and just spotted a spot where I hadn't change the schema in my test bed to the variable...
Revised here:
declare @my_schema nvarchar(15)
declare @sql_statement nvarchar(max)
declare @my_schema_id int
set @my_schema = 'rtf375'    -- CHANGE THIS TO THE SCHEMA YOU WANT TO CHANGE FROM.
set @my_schema_id = schema_id(@my_schema)

while (select count(*) from sys.tables where schema_id=@my_schema_id)>0
BEGIN 
   SELECT TOP(1) @sql_statement = 'ALTER SCHEMA dbo TRANSFER '+@my_schema+'.'+name
     from sys.tables
     where schema_id = @my_schema_id;
   EXEC (@sql_statement)
END

Open in new window

0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
If you execute the following query:
SELECT 'ALTER SCHEMA dbo TRANSFER rtf375.'+name
from sys.tables
where schema_id = SCHEMA_ID('rtf375')

Open in new window

It should generate the alter commands, which you should be able to copy and paste back into your query, and execute.
(Still looking to see if there is a way to execute them directly.)
0
 
wls3Commented:
If you have access to SQL Server Management Studio on the machine you can do an Edit Top 200 Rows from the Object Explorer. Once you have the basic object, display the SQL view and change it to ALTER type then specifying the code as listed above.  Reference this walk through:

http://learningpcs.blogspot.com/2011/11/sql-server-management-studio-enterprise.html
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Thank you for the opportunity to learn today.  Ran it through a couple tests on my test system, and it changes all the tables of schema given in @my_schema to dbo.

declare @my_schema nvarchar(15)
declare @sql_statement nvarchar(max)
declare @my_schema_id int
set @my_schema = 'rtf375'    -- CHANGE THIS TO THE SCHEMA YOU WANT TO CHANGE FROM.
set @my_schema_id = schema_id(@my_schema)

while (select count(*) from sys.tables where schema_id=@my_schema_id)>0
BEGIN 
   SELECT TOP(1) @sql_statement = 'ALTER SCHEMA dbo TRANSFER '+@my_schema+'.'+name
     from sys.tables
     where schema_id = SCHEMA_ID('car');
   EXEC (@sql_statement)
END

Open in new window

0
 
Abiel de GrootDeveloperAuthor Commented:
Many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.