Remove All Tables and Constraints from a Database Using T-SQL

You've had this problem before: You don't want to drop a database completely, but you do want to drop all the tables. Try to drop your tables in the wrong order and you're slapped with an error regarding referential constraints. I've created this script to ease the burden. It first drops all the constraints, and then it drops all the tables. Let me know if this doesn't work on your SQL Server database. Here's a warning for those who didn't bother to read this paragraph:

WARNING: The following script will delete all the tables in your database.

On to the script:

DECLARE @TableName NVARCHAR(MAX)

DECLARE @ConstraintName NVARCHAR(MAX)

DECLARE Constraints CURSOR FOR

 SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

 

OPEN Constraints

FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')

 FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

END

 

CLOSE Constraints

DEALLOCATE Constraints

 

DECLARE Tables CURSOR FOR

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

 

OPEN Tables

FETCH NEXT FROM Tables INTO @TableName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('DROP TABLE [' + @TableName + ']')

 FETCH NEXT FROM Tables INTO @TableName

END

 

CLOSE Tables

DEALLOCATE Tables