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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abiel de GrootDeveloperAuthor Commented:
Many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.