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

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

13 thoughts on “Change schema name on Tables and Stored procedures in SQL Server 2005”

  7. http://

    For tables and views you can use
    SELECT ‘ALTER SCHEMA NewSchema TRANSFER ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘OldSchema’

  9. http://

    Super, Great, saved lots of time changing owner for table and sprocs.

    For those who do not quite understand (be sure to change sys.Procedures p

    to sys.Tables p) to get your list of table change statements ready to copy from results pane to a new query window, then execute the whole list at once.

  10. Charles

    If you don’t quite get it (I didn’t without some additional research) there’s just a few more things you need to know…

    1). Change sys.Procedures to sys.Tables
    2). Change ‘CHANGE_ME_Username’ to the current name of the schema you are trying to rename
    3). You don’t need to change anything else
    4). Open a New Query pane and paste the script with your modifications into the Script pane and then execute it
    5). Do a Select All and copy everything into another (New) Query Pane
    6). Execute that

    All tables that previously had the old schema name will now have just ‘dbo’ in front of them instead of the old schema name which means they’ll be available to queries using just the table name rather than needed the reference to the old schema

  13. Nadir

    Thanks Charles it works

    SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Tables p
    INNER JOIN
    sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_USER’

Comments are closed.