This website uses cookies for statistics and user experience. By using this website we assume you accept to receive cookies.

SQL Server script to bulk change table schema

SQL Server script to bulk change table schema

(Moved from blogspot)

Below is a SQL script for quickly changing all tables from one schema to another, for example: myschema.customer to dbo.customer. Set your source and destination schemas below. I had to do this for a Confluence update I believe, and couldn't find a suitable update script on the web.

-- Bulk update schema on tables

-- Set source schema
DECLARE @SourceSchema varchar(50) SET @SourceSchema = 'myschema'
-- Set destination schema
DECLARE @DestSchema varchar(50) SET @DestSchema = 'dbo'

DECLARE @SourceSchemaID int SET @SourceSchemaID = (SELECT SCHEMA_ID FROM sys.schemas WHERE name = @SourceSchema)
DECLARE @DestSchemaID int SET @DestSchemaID = (SELECT SCHEMA_ID FROM sys.schemas WHERE name = @DestSchema)

-- Loop through tables
DECLARE @Table varchar(50)
DECLARE @PrevTable varchar(50) SET @PrevTable = ''
WHILE (1=1)
BEGIN
   SET @Table = (SELECT TOP 1 name from sys.tables WHERE schema_id = @SourceSchemaID AND name > @PrevTable ORDER BY Name)
   IF @Table IS NULL BREAK
   --PRINT 'ALTER SCHEMA ' + @DestSchema + ' TRANSFER ' + @SourceSchema + '.[' + @Table + ']'
   EXECUTE ('ALTER SCHEMA ' + @DestSchema + ' TRANSFER ' + @SourceSchema + '.[' + @Table + ']')
   SET @PrevTable = @Table
END


Posted on 15th November 2011 in Technical, SQL Server