Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

SQL Server - ALTER SCHEMA

24 October,2011 by Tom Collins

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 + '.' + QUOTENAME(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 + '.' + QUOTENAME(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mySchema' ORDER BY TABLE_NAME

Related Posts 

SQL SELECT INTO

ALTER AUTHORIZATION sql to change owner of a database

SQL Server ALTER DATABASE , change collation and exclusive lock


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - ALTER SCHEMA


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer