Chris Straw
SHARE:

Script to delete all objects from 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'