Here's a quick script I wrote to assist in cleaning up a SQL Server database of all tables and Stored Procedures.
WARNING:
THIS *WILL* REMOVE everything in the currently selected database, so USE WITH *EXTREME* CAUTION for heaven's sake! Dont blame me if you blow your company's database without a backup!
Replace
[DATABASE_NAME] with whichever DB you would like to clean. This takes on from my previously posted tip about using the
undocumented sp_MSforeachtable stored procedure to drop tables post. This is only tested in SQL Server 2005 & 2008, should work in 2000.
------------------------------------------------------
-- Database Cleaner-up-er for SQL Server 2005
------------------------------------------------------
-- Version: 1.0.0
-- Date: 15/09/2007
-- Author: Thushan Fernando (thushan [ at ] wsoftware [dot ] biz)
-- URL: http://blogs.developerfusion.co.uk/blogs/thushan/archive/2007/09/15/3320.aspx
--
-- Cleans a database removing all tables and stored
-- procedures.
--
-- THIS WILL DELETE / DESTROY ALL TABLES AND STORED
-- PROCEDURES. YOU HAVE BEEN WARNED HOMEY!!!
-------------------------------------------------------
USE [DATABASE_NAME]
GO
-- INIT
SET NOCOUNT ON
-- VARIABLES
DECLARE @COMMAND varchar(255) -- Command to execute
DECLARE @COUNTER int -- Counter used to iterate
DECLARE @spName varchar(255) -- Stored procedure name
-- DELETE ALL THE TABLES
-- First disable any constraints on that table
EXEC sp_MSforeachtable @command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
-- Drop the table
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
-- CREATE A TEMPORARY TABLE
CREATE TABLE #StoredProcedures
(ID int IDENTITY (1,1),
ProcName varchar(128) NOT NULL)
-- INSERT OUR LIST OF STORED PROCEDURES
INSERT INTO #StoredProcedures (ProcName)
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%' AND ROUTINE_TYPE = 'PROCEDURE'
-- Get the number of StoredProc's in the database
SELECT @COUNTER = MAX(ID) FROM #StoredProcedures
-- Iterate through the items
WHILE @COUNTER > 0
BEGIN
-- Get the name of the Object
SELECT @spName = ProcName FROM #StoredProcedures WHERE ID = @COUNTER
-- Make the command to execute
SELECT @COMMAND = 'DROP PROCEDURE ' + @spName
-- Execute the command
EXEC(@COMMAND)
-- Reduce the counter
SET @COUNTER = @COUNTER - 1
END
-- Cleanup the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
There's probably a better way of doing this - asside from
DROP DATABASE [DATABASE_NAME] (har-har!), but IWFM!!!