>SQL Server: How to Transfer All Objects of a Schema


>

Today, a colleague asked that “How he can transfer all objects of a schema to other one” First answer came in my mind that, “We must rename the existing schema” but I was not sure, because I never renamed a schema through t-sql in my carrier. But I was thinking that there should be a t-sql syntax to accomplish this task.
Unfortunately there is no such t-sql statement, using which we can rename a schema. We can only transfer objects of one schema to other using ALTER SCHEMA:
FOR EXAMPLE we need to transfer ‘Address’ table from ‘Sales’ schema to newly created ‘DailySales’
ALTER SCHEMA DailySales TRANSFER Sales.Address
And when, we need to transfer all objects of a schema (and there are dozens or hundreds of objects in a schema), it is really painful to write above alter schema line for every object. Following is the method which I like to use for such scenarios.
·   We need to transfer all objects from ‘Sales’ schema to our newly created ‘DailySales’
·   Execute follow query, and copy result set to a new query and execute to transfer all objects.
SELECT ‘ALTER SCHEMA DailySales TRANSFER Sales.’+name
FROM sys.objects
WHERE type IN (‘U’,‘V’,‘P’,‘Fn’)
AND SCHEMA_NAME(SCHEMA_ID) = ‘Sales’
Advertisements

Posted on February 7, 2011, in Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

  1. This is very good an eloquent.The only change was to put a (+ ';') after (+name) so as to terminate the individual command line.Thanks!Hank Freeman – hfreeman@msn.com

  2. Simple solution, very helpful! Does exactly what it says on the tin.

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

%d bloggers like this: