Monday, January 19, 2009

SQL Server 2005: Change schema for Tables and SPs

SQL Server 2005 copied the schema concept from Oracle. Earlier the user and schema used to be same for SQL 2000 but from 2005, these are two different things.
Now when we migrate a SQL 2000 DB to SQL 2005 server, we face a problem. We find that the objects i.e. Tables and Schemas are owned by the user and not [dbo].
So now to make [dbo] (or some other schema own it) execute the following scripts:

Table
SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'OldSchema'

SP
SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'OldSchema'