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
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = 'Sales'