2005: change all schema to dbo?

My hosting service moved my database to sql server 2005 and the schema
for the tables and procedures is no longer 'dbo'. I need to change all
the object owners back to 'dbo'. There was a script in sql 2000 to do
this, but not finding one for 2005. You would think people need to do
this often enough that there would be a menu command to do it in
management studio. I guess this is too obvious.
I can't find anything in BOL, all I know is from google. Some sort of
looping script over all the objects with ALTER SCHEMA? Or would there be
another trick to get the application working again?
Have a look in Books Online at ALTER SCHEMA ... TRANSFER
You could generate the list using:
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');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Paul" <invalid@.nospam.net> wrote in message
news:%23TBgQo%23aHHA.4140@.TK2MSFTNGP06.phx.gbl...
> My hosting service moved my database to sql server 2005 and the schema for
> the tables and procedures is no longer 'dbo'. I need to change all the
> object owners back to 'dbo'. There was a script in sql 2000 to do this,
> but not finding one for 2005. You would think people need to do this often
> enough that there would be a menu command to do it in management studio. I
> guess this is too obvious.
> I can't find anything in BOL, all I know is from google. Some sort of
> looping script over all the objects with ALTER SCHEMA? Or would there be
> another trick to get the application working again?
>
|||Perfect thanks. You just run the output of these select statements.
Aaron Bertrand [SQL Server MVP] wrote:
> Have a look in Books Online at ALTER SCHEMA ... TRANSFER
> You could generate the list using:
> 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');
>