Posted By

jprochazka on 08/13/11


Tagged

mssql


Versions (?)

Microsoft SQL Server: Rename a database schema.


 / Published in: SQL
 

Generates a set of queries to run which will change the schema for all items within a specified schema and change them to another.

  1. DECLARE @sourceschema VARCHAR(100)
  2. DECLARE @destinationschema VARCHAR(100)
  3. SET @sourceschema = 'source_schema'
  4. SET @destinationschema = 'destination_schema'
  5. SELECT 'ALTER SCHEMA ' + @destinationschema + ' TRANSFER [' + b.name + '].[' + a.name + ']' FROM sys.objects a JOIN sys.schemas b
  6. ON a.schema_id=b.schema_id WHERE a.type IN ('U','P','FN','V') AND b.name=@sourceschema

Report this snippet  

You need to login to post a comment.