Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

ALTER SCHEMA

Use this select statement to create a list of ALTER SCHEMA statements for all stored procedures in a SQL Server 2005 database. “dbo”  can be changed to whatever schema name required

USE myDb
GO
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Procedures p INNER JOIN 
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'mySchema'
order by p.Name

 

Use this select statement to create a list of ALTER SCHEMA statements for all tables and views in a SQL Server 2005 database. “dbo”  can be changed to whatever schema name required

 

USE myDb
GO
SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mySchema' ORDER BY TABLE_NAME
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.