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?

Are you sure you just haven't set your users default schema up incorrectly? — Nick.Mc, May 29 '15 at 05:28
The question is nearly two years old but as far as I can remember it wasn't related to user's default schema. — anar khalilov, May 29 '15 at 05:32
Readers - Also see this Microsoft answer: https://support.managed.com/kb/a100/how-to-change-schema-of-mssql-tables-stored-procedures-and-views-all-at-the-same-time.aspx — Yogi, May 07 '18 at 14:58
I faced the same issue recently. what causes this the schema of objects to be changed while creating a new DB instance with existing user, and assigning privileges to the new instance? — Mohamed Iqzas, Nov 14 '19 at 02:39

anar khalilov · Accepted Answer · 2015-05-29T05:28:23.777

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.

Nice solution. I used "WHERE SysSchemas.Name <> 'dbo'" instead to list all non-dbo bound objects. — Mackan, Nov 04 '14 at 10:14
For **all Tables** , check [this](http://stackoverflow.com/a/1149168/2218697) and [this](http://stackoverflow.com/a/10943302/2218697) to do it in single statement, hope helps some one. — Shaiju T, Jul 03 '16 at 09:45
want to transfer functions as well, you can add 'FN','TF' to in list. 'FN' for Scalar function, and 'TF' for Table function. for more, ref: https://msdn.microsoft.com/en-us/library/ms177596.aspx — Will Wu, Nov 16 '16 at 02:25

score 8 · Answer 2 · answered May 29 '15 at 13:52

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!

score 6 · Answer 3 · answered Apr 13 '17 at 07:33

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 ***'

score 4 · Answer 4 · edited May 29 '15 at 05:26

Thanks for the tip.. Here is my update to same, where I added a crlf to output as well as put brackets around the SchemaName and ObjectName, because some of the objects had a '-' in the name and the brackets solved that naming error.

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' +      DbObjects.Name + '];'
+ CHAR(13)+ CHAR(10)+ 'GO '+ CHAR(13)+ CHAR(10)
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'))

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

4 Answers4

Linked