How to change schema of all tables, views and stored procedures in MSSQL

Question

Recently we were having issues on our database server and after long efforts it was decided to change the database server. So we managed to restore the database on another server, change the connection string, etc. Everything was going as planned until we tried to access the website from a web browser.

We started getting errors about database objects not being found. Later we found out that it occured as a result of the modified schema name. Since there are hundreds of database objects (tables, views and stored procedures) in a Kentico database, it is not feasible to change all of them manually, one-by-one. Is there a practical way of doing this?

Answer 1

Yes, it is possible.

To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Running the above script will generate the SQL commands needed to transfer objects from one schema to another. Something like this:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer;

Now you can run all these generated queries to complete the transfer operation.

Answer 2

Here's the SQL I ran, to move all tables in my database (spread across several schemas) into the "dbo" schema:

DECLARE 
    @currentSchemaName nvarchar(200),
    @tableName nvarchar(200)

DECLARE tableCursor CURSOR FAST_FORWARD FOR 
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY 1, 2

DECLARE @SQL nvarchar(400)

OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER SCHEMA dbo TRANSFER ' + @currentSchemaName + '.' + @tableName
    PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName
END

CLOSE tableCursor 
DEALLOCATE tableCursor 

Phew!

Answer 3

You may use following script by just copy/paste for all objects

NOTE: You need to Change schema names in script !

DECLARE @OldSchema VARCHAR(200)
DECLARE @NewSchema VARCHAR(200)
DECLARE @SQL nvarchar(4000)
SET @OldSchema = 'dbo'
SET @NewSchema = 'Inf'

DECLARE tableCursor CURSOR FAST_FORWARD FOR 
    SELECT 'ALTER SCHEMA  ['+ @NewSchema +'] TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' AS Cmd
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    WHERE SysSchemas.Name = @OldSchema
    AND (DbObjects.Type IN ('U', 'P', 'V'))
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO  @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM tableCursor INTO  @SQL
END
CLOSE tableCursor 
DEALLOCATE tableCursor 
PRINT '*** Finished ***'