Monday, February 7, 2011

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'

5 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

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

    ReplyDelete
  3. Just an FYI:

    If you want to save yourself the second step of pasting and executing the result set separately, just direct the output into a cursor and run as a single batch as shown in the first listing below. If you anticipate using the code frequently, wrap it up in a sproc with the source and target schemas as input parameters. See examples two and three for the sproc code and execution syntax.

    ------ Cursor Example -------------------------------------

    DECLARE c_ALTSCHEMA CURSOR FOR
    SELECT 'ALTER SCHEMA DailySales TRANSFER Sales.'+name +';'
    FROM sys.objects
    WHERE type IN ('U','V','P','Fn')
    AND SCHEMA_NAME(SCHEMA_ID) = 'Sales'
    DECLARE @SQLStmt NVARCHAR(200)
    OPEN c_ALTSCHEMA
    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC(@SQLStmt)
    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
    END
    CLOSE c_ALTSCHEMA
    DEALLOCATE c_ALTSCHEMA

    ------ Sproc Example -------------------------------------

    CREATE PROC sp_xferschemaobjects
    @sourceSchema nvarchar(100),
    @targetSchema nvarchar(100)
    AS
    DECLARE c_ALTSCHEMA CURSOR FOR
    SELECT 'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @sourceSchema + '.'+name +';'
    FROM sys.objects
    WHERE type IN ('U','V','P','Fn')
    AND SCHEMA_NAME(SCHEMA_ID) = @sourceSchema
    DECLARE @SQLStmt NVARCHAR(200)
    OPEN c_ALTSCHEMA
    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC(@SQLStmt)
    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
    END
    CLOSE c_ALTSCHEMA
    DEALLOCATE c_ALTSCHEMA

    ------ Execute Sproc Syntax -------------------------------------

    EXEC sp_xferschemaobjects sourceSchema, targetSchema

    ------ End Code Listings ------------------------------------____-

    Thanks,
    Clark Froebe

    ReplyDelete
    Replies
    1. amazing you've saved me an hour! thanks

      Delete
    2. Tanks a lot!! u have saved my life :)

      Delete

All suggestions are welcome