Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL for renaming the database schema
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment