Change Ownership on ALL tables 2

[ecobb]

Hey,
Is there a command I can run to change the owner of every table in a database? I have a database that was set up with various owners on various tables (someone not knowing what they were doing) and it has over 500 tables it. I'd really rather not go through sp_changeobjectowner on every one of them.

Thanks,

Eric

 

[tlbroadbent]

The followig script changes the table owner for all tables in the current database to 'dbo' if not already 'dbo'. The script uses the undocumented system stored procedure sp_msforeachtable.

exec sp_msforeachtable
'If left(''?'',charindex(''.'',''?'')-1)<>''[dbo]''
exec sp_changeobjectowner ''?'',''dbo''' If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ecobb]

Thanks Terry!