SQL to change the schema on a series of tables

Posted:
24 Apr 2013 - 21:02 UTC.

When I'm releasing updates to a database I will release to a pre-release schema before dropping the live objects. Afterwards I will then change them over to the new schema. This is a sample script of how to change the owning schema across.

As with all SQL samples, you should be careful to read and understand what it does, and have a backup before attempting to run it. The script creates a list of tables that are in the dbo schema and loops through them changing the owner. There are multiple ways to do this, it's structured like this for ease of reading.

DECLARE @targetSchema AS VARCHAR(20) = 'schemaname'

DECLARE @transfers AS TABLE(

name VARCHAR(255)

)

INSERT INTO @transfers

SELECT name

from sys.tables

where type = 'U' and schema_id = 1

DECLARE @counter AS INTEGER = @@ROWCOUNT

WHILE @counter > 0

BEGIN

DECLARE @name AS VARCHAR(255)

SELECT TOP(1)

@name = name

FROM @transfers

EXEC('ALTER SCHEMA ' + @targetSchema + ' TRANSFER dbo.' + @name)

DELETE FROM @transfers where name = @name

SELECT @counter = COUNT(*) FROM @transfers

END

Share

Comments

There are no comments.

Posting Comments is disabled.