Change Schema Name on Tables and Stored Procedures in SQL Server 2005
By Steve Schofield
April 26, 2006
This article shows how 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.
Change stored procedure owner in SQL Server 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 the output in a new query window. After executing the statements,
refresh SQL Management studio; the stored procedures would be updated to dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.
Change table owner in SQL Server 2005
Click here for an article that describes the syntax,
it uses 'sp_changeobjectowner' which will not be supported in future
SQL Server revisions however it works in SQL Server 2005.
declare @OldOwner varchar(100) declare @NewOwner varchar(100) set @OldOwner
= 'OldOwner' set @NewOwner = 'NewOwner' s
elect 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', '''
+ @NewOwner + ''' go
from information_schema.tables where Table_schema = @OldOwner
Steve Schofield is a Senior Internet Support
Specialist with
ORCS Web, Inc.
- a company that provides managed hosting solutions for clients who develop and deploy their applications on Microsoft Windows platforms. Services include shared hosting, dedicated hosting, and webfarm hosting, with specialty in .Net, SQL Server, and architecting highly scalable solutions.