DBA Sensation

December 4, 2008

Change Schema Name or owner on Tables and Stored Procedures in SQL Server 2005

Filed under: 3. MS SQL Server — zhefeng @ 4:49 pm

i found a useful doc from http://www.orcsweb.com/articles/change_schema_name.aspx

“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

Advertisements

1 Comment »

  1. how do i fatch other schema data in my asp.net page of sql server2005

    Comment by sandeep — August 3, 2010 @ 11:44 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: