WayneS (9/14/2010)

---

assh1122 (9/14/2010)

---

Hi,

Thank you both for taking your time to answer my question. I could able to successfully change the table owner using the sp_changeobjectowner '[currentschema].[currenttable]' , 'dbo' command. Now I have to change the owner of more than 1000 tables. I am thinking of creating a stored procedure to iterate the sp_changeobjectowner command n no of times to make the owner change. I want to know if it is the right approach? I am new to SQL programming. Please let me know if there is any another approach to accomplish this? Your help is greatly appreciated.

There's an undocumented system stored procedure you can use:

execute sp_msforeachtable 'sp_changeobjectowner ''?'' , ''dbo'';print ''?'';'

The ? is replaced with the schema/name of the table, in the [] brackets. Those are all single-quotes there also... you need to double them up when using within a string.