SQL for renaming the database schema

gistfile1.txt

USE clickonpublish

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)

SET @OldSchema = 'dbo'
SET @NewSchema = 'clickonpublish'

DECLARE @sql AS varchar(MAX)

SET @sql = 'CREATE SCHEMA [' + @NewSchema + ']' + CHAR(13) + CHAR(10)

SELECT @sql = @sql + 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
     + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @OldSchema

PRINT @sql -- NOTE PRINT HAS AN 8000 byte limit - 8000 varchar/4000 nvarchar - see comments
IF (0=1) EXEC (@sql)