homebrew01 (9/14/2010)

---

Choose "Results to Text", then copy/paste the results into a new window.

declare @OldOwner varchar(100),  @NewOwner varchar(100)
	set @OldOwner = 'oldowner'
	set @NewOwner = 'dbo'

	select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''
	go'  
	from information_schema.tables where Table_schema = @OldOwner

You can skip the variables and hardcode the owner names in the script.

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.

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

WayneS (9/14/2010)

---

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.