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