Tuesday, October 21, 2014

Delete a SQL Server database schema with all its objects

Recently as part of R&D I had to delete all database schemas in a SQL Server Database. The major pain I foresee on identifying objects associated with it and deleting those in order. I was confident that somebody might have faced the same earlier and the script will be available as its, That's correct. I got a good link in first google itself. Its given below

http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/#comment-428

Really thanks to this guy. But when I tried deleting the schema in my database using this SP, I got an error saying that the schema cannot be dropped as there are some user defined table types inside it. The technique which this guy used is to get the objects of schema is to query the sys.objects and that never gives the User Defined Table Types inside the schema.

SELECT *
FROM sys.objects SO
WHERE SO.schema_id = schema_id(@SchemaName) order by name

This might also be faced by some other people so read some comments but no luck. So had to spend sometime on the query and added the code to delete UDTT too.

--Add DROP TYPE statements into table
INSERT INTO #dropcode
SELECT 'DROP TYPE '+ @SchemaName + '.'+name
FROM   sys.types
WHERE  is_table_type = 1 and schema_id=schema_id(@SchemaName)

File can be downloaded from here
 
Once again thanks to Ranjith the author of original post and hope he wont mind me changing his work and redistributing

1 comment:

Affity Solutions said...

Thanks for your ideas. You can also find the details on Affity Solutions, at the System Programming. The main object of the Affity Solutions is to provide quality web services and is among the few software development company in Nagpur.