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
 General SQL Server Forums
 New to SQL Server Administration
 schema owner change

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2012-01-20 : 05:32:46
Hi Team,
i've some hundred tables with schema abc_123. this needs to be changed to another schema xyz_123. kindly help.

ex: table name currently showing as abc_123.table_name
expected : xyz_123.table_name.

thanks in advance.

Note : I've tried to google, but i got the information to change to DBO. not for other schema. please help.

Arnav
Even you learn 1%, Learn it with 100% confidence.

biswajitdas
Starting Member

44 Posts

Posted - 2012-01-20 : 11:20:51
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'CHANGE_ME_Username'

check the below thread it will help you

http://www.iislogs.com/steveschofield/change-schema-name-on-tables-and-stored-procedures-in-sql-server-2005


Sr Sql server DBA/Artitech
Go to Top of Page
   

- Advertisement -