We have a SQL Server 2008 database that has some old tables and stored procedures that need to be dropped. We want to keep the newer tables, but just clean up the old no longer used objects.

My task is to build and check in a script that will do the drops, so my lead can test it against dev and qa, before it is run against prod.

Here is what I have created so far (mostly built from other SO thread answers).

This is a non-destructive script, it simply builds a resultset of DROP commands to be run later.

Once this script is run, from the resultset, you can click the last column, “DropCommand”, which will highlight that column, then copy and paste into a new query editor window, and now you have all of the DROP commands to run for the DBName and tables that you defined.

Notice the GroupID column in the #ObjectsToDrop temp table, which is used to order the results properly so that you drop sprocs first, then FK’s, then tables last.

I may add a TRY / CATCH with ROLLBACK, but this is what I have so far.

Please take a look and make any suggestions as to how it could be improved, or if I am forgetting anything, such as other objects that may need to be dropped.

/*************************************************/ /* DATABASE WHICH WE ARE CLEANING UP             */ /*************************************************/ DECLARE @DbName varchar(64); SET @DbName = 'YourDBName';  /*************************************************/ /* CREATE TEMP TABLE WITH TABLES OF INTEREST     */ /*************************************************/ CREATE TABLE #TableNames(TableName varchar(64)) INSERT INTO #TableNames (TableName) VALUES('Table1') INSERT INTO #TableNames (TableName) VALUES('Table2') INSERT INTO #TableNames (TableName) VALUES('Table3')   /******************************************************/ /* CREATE TEMP TABLE TO STORE OBJECTS TO DROP         */ /******************************************************/ CREATE TABLE #ObjectsToDrop(ObjectName varchar(128), TypeDesc varchar(32), GroupID int, DropCommand varchar(1024))  INSERT INTO #ObjectsToDrop SELECT '-', '-', 100, 'USE [' + @DbName + ']' INSERT INTO #ObjectsToDrop SELECT '-', '-', 101, 'GO' INSERT INTO #ObjectsToDrop SELECT '-', '-', 102, 'BEGIN TRANSACTION'   /********************************************************************************/ /* USING CURSOR LOOP THRU #TableNames TO BUILD DROP SCRIPTS                     */ /********************************************************************************/ SET NOCOUNT ON;  DECLARE @tableName varchar(64);  DECLARE doc_cursor CURSOR FOR        SELECT t.*       FROM #TableNames t      OPEN doc_cursor     FETCH NEXT FROM doc_cursor      INTO @tableName      WHILE @@FETCH_STATUS = 0       BEGIN           /************************************/         /* BUILD DROP PROCEDURE SCRIPTS     */         /************************************/         INSERT INTO              #ObjectsToDrop         SELECT              --o.name, o.type + ' - ' + o.type_desc, 200, 'DROP PROCEDURE dbo.' + o.name + ';'             o.name, o.type + ' - ' + o.type_desc, 200, '/*Need to Review*/ --DROP PROCEDURE dbo.' + o.name + ';'         FROM              sys.syscomments c         INNER JOIN              sys.all_objects o ON c.id = o.object_id         WHERE              c.text LIKE '%' + @tableName + '%'           /************************************/         /* BUILD DROP FK SCRIPTS            */         /************************************/         INSERT INTO             #ObjectsToDrop         SELECT             o.name, 'FK', 300, 'ALTER TABLE [' + s.name + '].[' + t.Name + '] DROP CONSTRAINT [' + o.name + ']'         FROM sys.foreign_key_columns fk         INNER JOIN sys.objects o             ON o.object_id = fk.constraint_object_id                 INNER JOIN sys.tables t             ON t.object_id = fk.parent_object_id         INNER JOIN sys.schemas s             ON t.schema_id = s.schema_id         WHERE              fk.referenced_object_id = (SELECT object_id                                          FROM sys.tables                                         WHERE name = @tableName)           /************************************/         /* BUILD DROP TABLE SCRIPTS         */         /************************************/         INSERT INTO             #ObjectsToDrop         SELECT              @tableName, 'Table', 400, 'DROP TABLE dbo.' + @tableName + ';'          FETCH NEXT FROM doc_cursor          INTO @tableName      END        CLOSE doc_cursor DEALLOCATE doc_cursor   /*************************************************/ /* A COUPLE MORE ROWS, THEN SELECT DROP SCRIPTS  */ /*************************************************/ INSERT INTO #ObjectsToDrop SELECT '-', '-', 900, 'ROLLBACK' INSERT INTO #ObjectsToDrop SELECT '-', '-', 901, '--COMMIT' SELECT * FROM #ObjectsToDrop ORDER BY GroupID, ObjectName   /*************************************************/ /* CLEAN UP                                      */ /*************************************************/ DROP TABLE #TableNames DROP TABLE #ObjectsToDrop