Ignatu.co.uk

Usage

Run this script against any user database and it will remove most user created objects.

You can change the types that it will remove by editing the list at configuration point 1. You may also need to supplement the "drop" prefixes at configuration point 2 (if the script is missing a type).

NB. If any of your tables have foreign key constraints you will probably need to run the script more than once to remove all of the objects.

Compatibility

The script has been tested with SQL Server 2005 and it should also work with 2000 and 2008.

I've deliberately avoided using the Information Schema Views in order to provide SQL Server 2000 compatibility.

Script

DECLARE @name VARCHAR(255) 
DECLARE @type VARCHAR(10) 
DECLARE @prefix VARCHAR(255) 
DECLARE @sql VARCHAR(255) 

DECLARE curs CURSOR FOR 
    SELECT [name], xtype  
    FROM sysobjects  
    WHERE xtype IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR') -- Configuration point 1          
    ORDER BY name 

OPEN curs 
FETCH NEXT FROM curs INTO @name, @type 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    -- Configuration point 2 
    SET @prefix = CASE @type  
        WHEN 'U' THEN 'DROP TABLE' 
        WHEN 'P' THEN 'DROP PROCEDURE' 
        WHEN 'FN' THEN 'DROP FUNCTION' 
        WHEN 'IF' THEN 'DROP FUNCTION' 
        WHEN 'TF' THEN 'DROP FUNCTION' 
        WHEN 'V' THEN 'DROP VIEW' 
        WHEN 'TR' THEN 'DROP TRIGGER' 
    END 

    SET @sql = @prefix + ' ' + @name 
    PRINT @sql 
    EXEC(@sql) 
    FETCH NEXT FROM curs INTO @name, @type 
END 

CLOSE curs 
DEALLOCATE curs