Drop all objects in SQL Server database that belong to different schemas?

Question

Is there a way to drop all objects in a db, with the objects belonging to two different schemas?

I had been previously working with one schema, so I query all objects using:

Select * From sysobjects Where type=...

then dropped everything I using

Drop Table ...

Now that I have introduced another schema, every time I try to drop it says something about I don't have permission or the object does not exist. BUT, if I prefix the object with the [schema.object] it works. I don't know how to automate this, cause I don't know what objects, or which of the two schemas the object will belong to. Anyone know how to drop all objects inside a db, regardless of which schema it belongs to?

(The user used is owner of both schemas, the objects in the DB were created by said user, as well as the user who is removing the objects - which works if the prefix I used IE. Drop Table Schema1.blah)

Answer 1

Use sys.objects in combination with OBJECT_SCHEMA_NAME to build your DROP TABLE statements, review, then copy/paste to execute:

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.objects
 WHERE type_desc = 'USER_TABLE';

Or use sys.tables to avoid need of the type_desc filter:

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.tables;

SQL Fiddle

Answer 2

Neither of the other questions seem to have tried to address the all objects part of the question.

I'm amazed you have to roll your own with this - I expected there to be a drop schema blah cascade. Surely every single person who sets up a dev server will have to do this and having to do some meta-programming before being able to do normal programming is seriously horrible. Anyway... rant over!

I started looking at some of these articles as a way to do it by clearing out a schema: There's an old article about doing this, however the tables mentioned on there are now marked as deprecated. I've also looked at the documentation for the new tables to help understand what is going on here.

There's another answer and a great dynamic sql resource it links to.

After looking at all this stuff for a while it just all seemed a bit too messy.

I think the better option is to go for

ALTER DATABASE 'blah' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
drop database 'blah'

create database 'blah'

instead. The extra incantation at the top is basically to force drop the database as mentioned here

It feels a bit wrong but the amount of complexity involved in writing the drop script is a good reason to avoid it I think.

If there seem to be problems with dropping the database I might revisit some of the links and post another answer