Clear all objects from database

Today I have been working on web deploy solution, and needed to clear databse.
Drop database does not work for me, because the files still hang around. And I want to reuse the implementation for every build I do. This solution come from the need of regular deploy, from my build server.


I need to clear:

1. Stored procedures if any
2. Foreign keys
3. Primary key constaints
4. Tables

My implementation works with only [dbo] schema, but you can specify the block for each chema you need.

Original source for this you can find of course on Stack overflow

My modification is make sure that database name is specified for every schema, in order to satisfy DB admins, that in case of selecting wrong schmea, the implementation does not remove everything.

Now SQL:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'HomeSite')
BEGIN

    /* ====================================================*/
    /*           Drop all non-system stored procs          */
    /* ====================================================*/
        
        /* Drop all non-system stored procs */
        DECLARE @StoredProcName VARCHAR(128)
        DECLARE @StoredProcSQL VARCHAR(254)

        SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

        WHILE @StoredProcName is not null
        BEGIN
            SELECT @StoredProcSQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@StoredProcName) +']'
            EXEC (@StoredProcSQL)
            PRINT 'Dropped Procedure: ' + @StoredProcName
            SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @StoredProcName ORDER BY [name])
        
        END
        
    /* ====================================================*/
    /*           Drop all Foreign Key constraints          */
    /* ====================================================*/        
        
        DECLARE @ForeignKeyName VARCHAR(128)
        DECLARE @ForeignKeyConstraint VARCHAR(254)
        DECLARE @ForeignKeySQL VARCHAR(254)

        SELECT @ForeignKeyName = (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 @ForeignKeyName is not null
        BEGIN
            SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @ForeignKeyConstraint IS NOT NULL
            BEGIN
                SELECT @ForeignKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@ForeignKeyName) +'] DROP CONSTRAINT [' + RTRIM(@ForeignKeyConstraint) +']'
                EXEC (@ForeignKeySQL)
                PRINT 'Dropped FK Constraint: ' + @ForeignKeyConstraint + ' on ' + @ForeignKeyName
                SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @ForeignKeyConstraint AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @ForeignKeyName = (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
    
    /* ====================================================*/
    /*           Drop all Primary Key constraints          */
    /* ====================================================*/
    
        DECLARE @PrimaryKeyName VARCHAR(128)
        DECLARE @PrimaryKeyConstraint VARCHAR(254)
        DECLARE @PrimaryKeySQL VARCHAR(254)

        SELECT @PrimaryKeyName = (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 @PrimaryKeyName IS NOT NULL
        BEGIN
            SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @PrimaryKeyConstraint is not null
            BEGIN
                SELECT @PrimaryKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@PrimaryKeyName) +'] DROP CONSTRAINT [' + RTRIM(@PrimaryKeyConstraint)+']'
                EXEC (@PrimaryKeySQL)
                PRINT 'Dropped PK Constraint: ' + @PrimaryKeyConstraint + ' on ' + @PrimaryKeyName
                SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @PrimaryKeyConstraint AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @PrimaryKeyName = (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
        
    
        
    /* ====================================================*/
    /*           Finally drop all tables                           */
    /* ====================================================*/
        DECLARE @TableName VARCHAR(128)
        DECLARE @TableSQL VARCHAR(254)

        SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

        WHILE @TableName IS NOT NULL
        BEGIN
            SELECT @TableSQL = 'DROP TABLE [dbo].[' + RTRIM(@TableName) +']'
            EXEC (@TableSQL)
            PRINT 'Dropped Table: ' + @TableName
            SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @TableName ORDER BY [name])
        END    
    
END
GO