How can I rename a schema in SQL Server 2005?
+2
A:
If you have a large number of objects in a schema, you can use something like this to generate all the changes automatically (it only does tables and views, so before you run it, you might need to expand it to SPs, UDFs, etc.)
USE SandBox
DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)
SET @OldSchema = 'dbo'
SET @NewSchema = 'StackOverflow'
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
SET @sql = @sql + 'DROP SCHEMA [' + @OldSchema + ']'
PRINT @sql
IF (0=1) EXEC (@sql)
Cade Roux
2008-11-25 15:28:47
+1. Thanks Cade, you saved me a bunch of work!
p.campbell
2009-10-27 03:25:01
This doesn't work very well if the views contain references to the oldschema in them, unfortunately :(
mgroves
2010-10-15 14:58:00
@mgroves Yes, but objects in a schema do not require other objects in the same schema to be prefixed, so that's probably a design smell in the first place. You could attempt to catch that by using the view or proc code (stored in the metadata) to look for potential misuses of prefixes. You can catch potential parsing problems problems after a mass move by attempting to refresh all the (non-deterministic) SQL modules.
Cade Roux
2010-10-15 15:53:21