A Comprehensive Guide to Cleaning SQL Server Databases with T-SQL

Introduction

In database management, there is often a need to clean up unused databases rather than creating new ones, saving time and resources. Users may face delays waiting for database administrators, making an automated cleanup script essential. In SQL Server 2005 and later, due to complex dependencies between objects, such as foreign key constraints referencing tables, using a single DROP statement directly can cause errors. This article, based on community best answers, provides a comprehensive T-SQL script that sequentially drops all user objects, ensuring proper order to avoid dependency issues.

Challenges of Dependencies

In SQL Server, database objects like tables, stored procedures, and views have interdependencies. For example, a table might be referenced by a foreign key constraint, or a stored procedure may depend on a specific table. If tables are dropped without first handling constraints, errors occur. Referencing Microsoft documentation, the DROP TABLE statement cannot be used on tables referenced by FOREIGN KEY constraints; the constraints or referencing tables must be dropped first. This highlights the importance of managing dependency order in cleanup scripts.

Comprehensive T-SQL Cleanup Script

The following script is rewritten based on deep understanding, ensuring clarity and compatibility with SQL Server 2005. It drops stored procedures, views, functions, foreign key constraints, primary key constraints, and tables in sequence, using loops and dynamic SQL for each object. The script assumes objects are in the dbo schema but can be extended for other schemas.

/* Drop all non-system stored procedures */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SET @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) + ']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SET @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) + ']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SET @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) + ']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all foreign key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SET @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all primary key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SET @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SET @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) + ']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Step-by-Step Code Analysis

The script uses DECLARE statements to define variables and SELECT loops to retrieve object names. For instance, in the stored procedures section, it selects objects of type 'P' from the sysobjects system view and constructs DROP statements using dynamic SQL. Loops ensure all objects are processed sequentially, using the > operator to iterate in name order, preventing omissions. Each section is separated by GO to batch process, ensuring previous operations complete before the next. Print statements provide feedback for debugging.

Alternative Methods Comparison

Other answers, such as Adam Anderson's script, use sys views (e.g., sys.procedures, sys.tables) and support multiple schemas, but this script focuses on the dbo schema for simplicity. The sys views approach builds a single dynamic SQL string for execution, which may be more efficient, but the step-by-step method here is easier to understand and modify. Users can choose based on needs, such as adding multi-schema support in complex environments.

Best Practices and Considerations

Before executing the cleanup script, always back up the database to prevent data loss. Referencing Microsoft documentation, DROP TABLE requires ALTER permission on the schema or CONTROL permission on the table. Validate the script in a test environment to avoid accidental production issues. Additionally, SQL Server may delay space deallocation for large tables, so performance impacts should be noted. For complex dependencies, use sys.dm_sql_referencing_entities to check references beforehand.

Conclusion

The T-SQL script provided in this article is a reliable tool for database cleanup, safely dropping objects by handling dependencies correctly. It is compatible with SQL Server 2005 and later, enabling users to automate cleanup processes and improve efficiency. Future extensions could include support for triggers, user-defined types, and other objects for broader coverage.