Code to change schema in SQL 2005 1

[MinalMoe]

I have some tables in SQL 2005 belonging to a schema customers, which need to be changed to a dbo schema.

After Googling have tried

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');

The first part seems to do something but nothing changes. I know its almost there.

Any ideas - thanks

 

[ptheriault]

If you run the code you have posted it is going to create a script for you to run that will ALTER your schema. You must run the results from your first select in a query window or you could just run the ALTER SCHEMA command.

ALTER SCHEMA Customers TRANSFER dbo

- Paul
- If at first you don't succeed, find out if the loser gets anything.