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'
This is very good an eloquent.
ReplyDeleteThe only change was to put a (+ ';') after (+name) so as to terminate the individual command line.
Thanks!
Hank Freeman - hfreeman@msn.com
Simple solution, very helpful! Does exactly what it says on the tin.
ReplyDeleteJust an FYI:
ReplyDeleteIf 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
amazing you've saved me an hour! thanks
DeleteTanks a lot!! u have saved my life :)
Delete