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'