Sunday, September 9, 2007

SQL Server 2005 Change all objects to dbo schema

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.views
WHERE schema_id != SCHEMA_ID('dbo');

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.procedures
WHERE schema_id != SCHEMA_ID('dbo');

No comments: