2

I have restored a database from a backup onto a new server, and I cannot delete the old schema from the database. I have created a NEW user for this database. The NEW user is named differently (to make things clearer for other developers), so I have no use for this schema. I DID manage to delete the USER finally, but the schema won't go away.

The error I receive is "Cannot drop schema '' because it is being referenced by object ''

Running this script:

select * from sys.objects where schema_id = 5

...shows all the objects this schema 'owns'.

My problem is I cannot find a way to change the ownership of these objects. There must be a way? Thanks all

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Kolten
  • 3,495
  • 5
  • 42
  • 59
  • Was the backup from a SQL Server 2000 server installation - or from another 2005 installation? – dommer May 28 '09 at 21:46
  • Yes, I am moving from SQL 2000 to SQL 2005, so the backup is of the SQL 2000 variety, moving into SQL 2005 environment – Kolten May 28 '09 at 21:54

1 Answers1

5

The syntax to move object Thing from schema Source to schema Destination is:

ALTER SCHEMA Destination TRANSFER Source.Thing

Here's SQL to generate a script to transfer all objects to the dbo schema:

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

To just transfer one particular schema:

SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_name(schema_id) = 'OldSchema'
Andomar
  • 232,371
  • 49
  • 380
  • 404