Change table schema in MS SQL

When you copy a database from your local development PC or another hosting company or from a database you already host with us the chances are that the schema for your objects, like tables, will have a different owner/user to the one you have created and therefore your code won't work.

Here is a Microsoft MS SQL script for altering the schema on tables.

DECLARE @sql nVARCHAR(MAX)
DECLARE @table nVARCHAR(MAX)
DECLARE @oldSchemaName nVARCHAR(MAX)
DECLARE @newSchemaName nVARCHAR(MAX)

SET @oldSchemaName = 'test'
SET @newSchemaName = 'live'

WHILE EXISTS(select * FROM sys.tables WHERE schema_name(schema_id) = @oldSchemaName) BEGIN
    SELECT @table = name FROM sys.tables 	  
    WHERE object_id in(SELECT min(object_id) FROM sys.tables WHERE schema_name(schema_id)  = @oldSchemaName)
	  
    SET @sql = 'alter schema ' + @newSchemaName + ' transfer ' + @oldSchemaName + '.' + @table
	
    --Print code for reference	
    PRINT @sql

    EXEC(@sql)
END

Return to Category