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
ALTER AUTHORIZATION sql to change owner of a database
SQL Server ALTER DATABASE , change collation and exclusive lock
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |