trjhtr

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.

NOTE: I ditched the cursor in favor of a while loop.

ANOTHER NOTE: The requirement changed to drop all sprocs except for a select few.

/*************************************************/
/* DATABASE WHICH WE ARE CLEANING UP */
/*************************************************/
USE YourDBName
GO

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 CLUSTERED INDEX idx_tmp on #TableNames(TableName) WITH FILLFACTOR = 100
/*
Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. 
Since you know the data in this column, you can set the fill factor to 100% to get the best read times.
*/

/******************************************************/
/* 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 WHILE LOOP THRU #TableNames TO BUILD DROP SCRIPTS */
/********************************************************************************/

SET NOCOUNT ON

DECLARE @iterator INT; --iterator
DECLARE @rowCount INT; --rowcount
DECLARE @tableName VARCHAR(64);
SET @iterator = 1 --initialize

SET @rowCount = @@ROWCOUNT --SCOPE_IDENTITY() would also work

WHILE @rowCount > 0
 BEGIN
 SELECT TOP 1 @tableName = TableName FROM #TableNames
 SET @rowCount = @@ROWCOUNT --ensure that we still have data

 IF @rowCount > 0
 BEGIN
 /************************************/
 /* BUILD DROP PROCEDURE SCRIPTS */
 /************************************/
 INSERT INTO 
 #ObjectsToDrop
 SELECT 
 --o.name, o.type, 200, '/*Need to Review*/ --DROP PROCEDURE dbo.' + o.name + ';'
 o.name, o.type, 200, 'DROP PROCEDURE dbo.' + o.name + ';'
 FROM
 sysobjects o
 WHERE
 o.type = 'P'
 AND
 ObjectProperty(id, 'IsMSShipped') = 0
 AND 
 o.id NOT IN (SELECT major_id FROM sys.extended_properties WHERE name = N'microsoft_database_tools_support' AND minor_id = 0 AND class = 1)
 AND
 o.name NOT IN ('SProc1', 'SProc2', 'SProc3')


 /************************************/
 /* 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 */
 /************************************/
 IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID('dbo.' + @tableName) AND Type = N'U')
 BEGIN
 INSERT INTO
 #ObjectsToDrop
 SELECT 
 @tableName, 'Table', 400, 'DROP TABLE dbo.' + @tableName + ';'
 END

 DELETE FROM #TableNames WHERE TableName = @tableName
 END
 END


/*************************************************/
/* A COUPLE MORE ROWS, THEN SELECT DROP SCRIPTS */
/*************************************************/
INSERT INTO #ObjectsToDrop SELECT '-', '-', 900, 'ROLLBACK'
INSERT INTO #ObjectsToDrop SELECT '-', '-', 901, '--COMMIT'
SELECT DISTINCT * FROM #ObjectsToDrop ORDER BY GroupID, ObjectName


/*************************************************/
/* CLEAN UP */
/*************************************************/
DROP TABLE #TableNames
DROP TABLE #ObjectsToDrop