CODEWITHSUNDEEP
sqlsql-serversql-server-2005
corymathews
corymathews

Reputation: 12619

SQL Completely Empty Database

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

Upvotes: 11

Views: 62082

Answers (8)

trondulseth
trondulseth

Reputation: 87

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

Upvotes: 4

David Hedlund
David Hedlund

Reputation: 129832

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 ] [;]

Upvotes: 3

Rod
Rod

Reputation: 1571

Drop and recreate DB:

use master
IF EXISTS(select * from sys.databases where name='YourDBName')
DROP DATABASE YourDBName
CREATE database YourDBName

Upvotes: 1

ramnik
ramnik

Reputation: 545

If you have your scripting process automated per object, you should be able to wrap the entire process in a transaction and provide error handling for each DDL statment. This also works well when applying schema updates. Please note, this is based upon upgrade scripts that Red-Gate SQL Compare generates.

Header:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

Footer:

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
    PRINT 'Script succeeded'
    COMMIT TRANSACTION
END
ELSE BEGIN 
    PRINT 'Script failed'
END
GO
DROP TABLE #tmpErrors

Wrapper (For each database object):

/* Insert Data Definition here then append with...*/
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

Upvotes: 0

A-K
A-K

Reputation: 17090

Red Gate's SQL Compare will do it for you, it is a great tool.

Upvotes: 0

Jonathan
Jonathan

Reputation: 12015

I'm not sure if this is what you're looking for, but for each object, you could test if exist before creating it. Then you could run again the script if it fails in the middle.

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(@Object) AND type = (@Type))

...

Upvotes: -1

David M
David M

Reputation: 72930

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

Upvotes: 2

Robin Day
Robin Day

Reputation: 102578

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.

Upvotes: 7

Related Questions