drop schema and its objects

I want to be able to drop a schema and all its objects if they exist. Can someone help me with such a stored procedure. I see the sql server does not allow dropping schema directly if it contains some objects.

It is not always true. if you use indexed views, foreign keys then you are correct.

You can use sp_depends 'Your table name' to find all the dependent views/sps & others..

|||

I think I did not explain my question clearly.

I am using :

Code Snippet

DROP SCHEMA [new]

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'new' because it is being referenced by object 'cattr'.

I want to be able to drop the schema, even if some objects reference it. If it is mandatory to first drop all objects that the schema contains, then how can I programmitically delete all such objects and then delete the schema ?

So given a schema name the stored procedure should be able to drop the schema.

|||

Is there any way to do the above mentioned.

|||

You could use following statement for determining objects in schema and there types:

Code Snippet

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<SCHEMA NAME>'

Then, you could use cursor for calling needed DROP statements

|||

I think there is one problem with the above approach. We need to drop tables in a schema in a particular order as there may be referencences, foreign key relationships etc.

How do I determine the order ?

|||

You could delete constrains at first, then delete tables.

Also you could include each DROP statement in TRY/CATCH block and when some DROP fail analyze error message

|||

Or even try following:

Code Snippet

declare @.cnt int

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

--iterate while all objects deleted

while @.cnt >0

Begin

--Drop objects with cursor

--Each drop statemnt include into TRY/CATCH block, but ignore error

--New objects count

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

end

|||

>> obj.type, obj.name

How do I construct the drop statement from these ?

Say I got 'U' as type and 'cattr' as table name, I need the statement "drop table cattr"

But how to get the mapping between type 'U' and what it represents 'table'

or do I have write explit if else statements like

if type = 'U'

drop table

elsif type = 'P'

drop procedure....

But that's going to be a long list.

|||

I am not sure as to why it is so difficult to drop a schema.... especially when someone has permissions to do it. When we can drop a table eventhough it contains some data, why can't we drop a schema even if it contains some objects ?

And How do I construct drop statements for each object , the problem which I described above ?

|||

You could use dynamic sql for constructing drop statements.

Use CASE statement for constructing varchar variable:

@.dropQuery = 'DROP '+<case statements that returns TABLE, CONSTRAINT etc>+' '+obj.name

Then: EXECUTE(@.dropQuery)