TSQL to clear a database's schema in sql-server?

Question

I'd like to clear a database's schema on my SqlServer instance. What tsql should I use?

By schema I mean tables, constraints, etc. I want the result to be similar to if I created a new database, however I don't want to actually drop and create the database.

Why:

For those curious, I need to empty the schema without dropping because of the way the database is being isolated for unit tests. Before running my tests a snapshot of the database is saved. After each test runs, this snapshot is restored. I can only ensure consistent state across unit tests if I keep my db operations within the scope of the database. Dropping/Create the database is outside of the db's scope (its in the master's scope).

In this case, I need to assert that an expected thing happens when the schema is empty. Emptying the schema via sql keeps the testing methodology consistent: do basically whatever you want to the db, exercise it, restore it.

Raj More's answer got me started. I was hoping someone could short circuit the processes.

Answer 1

You can use the INFORMATION_SCHEMA set of views to generate SQL Scripts to drop all the objects.

You don't have to drop items like indices, triggers, constraints, because they get dropped when you drop the table that they are attached to.

Brute Force Alert

Now tables themselves are tricky because of relationships.

If you separate each drop statement with a GO, you can keep running the script until you have no errors, and then you will have a clean slate.

UnbruteForcing based on feedback

Now, if delete all the foreign keys first, then you can drop all tables in a single go.

Answer 2

Figured I'd share what I ultimately came up with. This script creates a cursor to loop over the tables in the db's INFORMATION_SCHEMA. It does 3 passes over the tables dopping Foreign Keys, then Primary Keys, and finally the tables themselves. Its based on Raj More's idea and considers devio's comment.

-- Helper Procedure
CREATE PROC #DropConstraints
  @tableSchema nvarchar(max),
  @tableName nvarchar(max),
  @constraintType nvarchar(20)
AS
BEGIN
  DECLARE @cName nvarchar(max);

  DECLARE constraint_cursor CURSOR FOR
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE 
      CONSTRAINT_TYPE = @constraintType
      AND TABLE_NAME = @tableName
      AND TABLE_SCHEMA = @tableSchema

  OPEN constraint_cursor

  FETCH NEXT FROM constraint_cursor INTO @cName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE ' + @tableSchema + '.' + @tableName + ' DROP CONSTRAINT ' + @cName);
    FETCH NEXT FROM constraint_cursor INTO @cName
  END

  CLOSE constraint_cursor
  DEALLOCATE constraint_cursor
END
GO

-- DROP DATABASE TABLES
BEGIN TRANSACTION
  DECLARE @tableSchema varchar(max), @tableName varchar(max);

  -- Setup Cursor for looping
  DECLARE table_cursor SCROLL CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES

  OPEN table_cursor

  -- Drop Foreign Keys
  FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN  
    EXEC #DropConstraints @tableSchema, @tableName, 'FOREIGN KEY';

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Drop Primary Keys
  FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC #DropConstraints @tableSchema, @tableName, 'PRIMARY KEY';

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Drop Tables
  FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('DROP TABLE ' + @tableSchema + '.' + @tableName);

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
  END

  -- Cleanup
  CLOSE table_cursor
  DEALLOCATE table_cursor
COMMIT TRANSACTION
GO