Drop all objects in database?

I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:

1. If I run this command

EXEC sp_MScheck_uid_owns_anything 5

I get this weird error message:

"The user owns objects in the database and cannot be dropped."

Not sure why that is since I'm just trying to list the objects, not drop them.

2. I tried running a simple query to get the objects from the system table instead:

SELECT * from [dbo].[sysobjects] where uid = 5

This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:

The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.

Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!

-CliffUse the TYPE column of sysobjects to filter out foreign keys, which should drop automatically when the tables are dropped.|||Thanks for responding. Your approach requires that the tables be dropped in a particular order so that there won't be any constraint violations, right? I was hoping to avoid that by deleting all the foreign keys first and then dropping all the tables...|||select 'alter table ' + object_name (fkeyid) + ' drop constraint ' + object_name (constid)
from sysreferences|||Thanks, that's getting me closer! I'll let you know how it goes ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1

-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1


-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)

hmmmmmmmm cursorssssssssss, im loving it!|||Bah! The performance problems will be lost in the wash of "Hey, where'd my table go?" complaints.

Hopefully, you have everything owned by dbo. If not, you will have to go back, and add in the user_name(uid) function.