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.
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 |
|
|
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? |
|
|
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/readre 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.... |
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2005-06-09 : 08:26:26
|
Ah it's undocumented, couldn't find it :-)Thanks |
|
|
|
|
|