TSQL clear database schema in sql server?
I want to clear the database schema on my SqlServer instance. Which tsql should i use?
By schema, I mean tables, constraints, etc. I want the result to be similar to what if I created a new database, however I don't want to actually give up and create the database.
Why:
For the curious, I need to clean up the schema without dropping it due to how the database is isolated for unit tests. A snapshot of the database is saved before running my tests. This snapshot is restored after each test. I can only ensure consistent state across all unit tests if I store my database operations within the database. Dropping / creating a database outside the db scope (its in the main scope).
In this case, I need to assert that the expected thing happens when the schema is empty. Emptying the schema through sql maintains the compatibility of the testing methodology: do basically whatever you want to the db, execute, restore.
Raj. Another answer got me started. I was hoping someone could short-circuit the processes.
source to share
Realized that I would share what I eventually came up with. This script creates a cursor to cycle through the tables in the INFORMATION_SCHEMA db. It performs 3 passes over the tables, closing foreign keys, then primary keys, and finally the tables themselves. Its based on an idea by Raj More and is reviewed by deio'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
source to share
You can use the INFORMATION_SCHEMA view set to generate SQL scripts to delete all objects.
You don't need to drop items like indexes, triggers, constraints, because they are dropped when the table they are bound to is dropped.
Brute force warning
Now the tables themselves are complex due to the relationships.
If you strip each drop statement with GO, you can keep running the script until you have errors and then you have a clean slate.
Feedback-based UnbruteForcing
Now, if you drop all foreign keys first, you can drop all tables in one go.
source to share