Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Change owner of all tables

Author  Topic 

Rauken
Posting Yak Master

108 Posts

Posted - 2005-06-09 : 05:28:02
I know I can change the owner of an object by using sp_changeobjectowner. I need to change the owner of all my tables, stored procedures and views to dbo. How do I use schema to do that?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-09 : 06:15:51
well for sprocs and views is best to script them all then find replace your owner and then create them again.
for tables you can use sp_MSforeachtable sproc:
exec sp_MSforeachtable "exec sp_changeobjectowner '?' , 'dbo'"


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Rauken
Posting Yak Master

108 Posts

Posted - 2005-06-09 : 07:17:58
How can I loop all the tables, sp's and get their names?

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-09 : 07:36:53
sp_MSforeachtable....loops for all tables....it's an undocumented MSSQL system procedure...but it can be viewed/read

re SP's you may have to build a master list of sp's (script all objects in EM...and remove everything but the SP names!!)...and do a lot of appending inside notepad!

it is a once off job after all....
Go to Top of Page

Rauken
Posting Yak Master

108 Posts

Posted - 2005-06-09 : 08:26:26
Ah it's undocumented, couldn't find it :-)

Thanks
Go to Top of Page
   

- Advertisement -