SQL Completely Empty Database

Question

I am writing an install script that installs all tables, stored procedures, views, full text indexs, users ect.

It all works fine if the user has all the correct permissions and the script runs from start to finish. However if the script dies somewhere midway through then it cannot just be run again.

To accomplish this I want to basically return the database to a "brand new" state where it has nothing.

I realize how to drop each table/sp/view.. on their own but I am looking for a more general way to reset the database.

I also need to be able to delete Fulltext Catalogs and users.

Thanks for any help.

Running SQL Server 2005

Answer 1

Can you run the entire script within a transaction and then call a rollback if it fails?

A CREATE TABLE can be rolled back just as easily as an INSERT/UPDATE.

Answer 2

Sounds like a job for Drop Database:

Warning: It doesn't EMPTY the database as the question asks, but it DELETES it, so be sure you have permissions to create a new one.

-- SQL Server Syntax  
DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]

Answer 3

The following works at least in SQL Server 2017. I have no way to test it in 2005.

/*
  Description: This script will remove all tables, views, functions, stored procedures and user defined types from a database.
*/
        declare @n char(1)
        set @n = char(10)

        declare @stmt nvarchar(max)

        -- procedures
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.procedures


        -- check constraints
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
        from sys.check_constraints

        -- functions
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop function [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.objects
        where type in ( 'FN', 'IF', 'TF' )

        -- views
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop view [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.views

        -- foreign keys
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
        from sys.foreign_keys

        -- tables
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop table [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.tables

        -- user defined types
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop type [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.types
        where is_user_defined = 1


        exec sp_executesql @stmt

Answer 4

Brand new containing nothing? Drop the database and recreate it if you have permissions to do this.