0

Script to delete all objects from SQL Server

Posted by Chris Straw on February 2, 2012 in SQL Server |

Today I accidentally ran a script on master instead of the DB desired.  So, I created a script that would create the drop statements for those objects.  If some of the objects depend on others, you may have to run the statements a few times to get everything to drop.


SELECT 'Drop Procedure [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.type = 'p'
 
--Drop FK
SELECT 'ALTER TABLE [' + SCHEMA_NAME(tb.schema_id) + '].[' + OBJECT_NAME(F.PARENT_OBJECT_ID)+ '] DROP CONSTRAINT [' + F.NAME + ']'
FROM SYS.FOREIGN_KEYS AS F
JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id
ORDER BY SCHEMA_NAME(TB.schema_id), OBJECT_NAME(F.PARENT_OBJECT_ID)
 
 
SELECT 'Drop View [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.type = 'V'

 
select 'Drop Function [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.type = 'FN'
 
 
 
select 'Drop Table [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.type = 'U'
 
 
 
 
select 'Drop Default [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.type = 'D'

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2011-2019 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.