Change schema name on Tables and Stored procedures in SQL Server 2005

Tags: SQL

Dec 31 2005 12:46 PM

I was trying to change the ‘schema’ name on an object from ‘username’ to ‘dbo’ in SQL Server 2005. 
The script generates code you have to copy, paste and run on the database. 
Thanks to the DBA gurus on the [email protected] list that provided the syntax. 
I encourage everyone looking for SQL help to check out the lists on
SQLAdvice.com http://sqladvice.com/lists/category.aspx?c=32 

Change Stored procedures in sql 2005

‘Note this is the raw query
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’

   It would create this kind of output.

  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors1
  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors2
  • ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors3

You would run in a new query window, after this refresh SQL Management studio
and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.

Change Table in sql 2005

Here is an article that describes the syntax, it uses the ‘sp_changeobjectowner’
which isn’t real “sql2005’ish” but works.  

declare @OldOwner varchar(100) declare @NewOwner varchar(100) 
set @OldOwner = 'OldOwner' set @NewOwner = 'NewOwner'
 
select 'sp_changeobjectowner ''[' + table_schema + '].[' + 
table_name + ']'', ''' + @NewOwner + ''' go 
 from information_schema.tables where Table_schema = @OldOwner