Delete and Drop All Tables from SQL Server Database

Posted At : July 20, 2009 2:01 AM | Posted By : Kumar
Related Categories: Other, SQL

This post is more for record keeping. Found this useful little SQL on another site and posting it here will make it accessible to me forever.

view plain print about

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR 

SET @Cursor = CURSOR FAST_FORWARD FOR 

SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql 

WHILE (@@FETCH_STATUS = 0) 

BEGIN 

Exec SP_EXECUTESQL @Sql

FETCH NEXT FROM @Cursor INTO @Sql 

END

CLOSE @Cursor DEALLOCATE @Cursor 

GO

EXEC sp_MSForEachTable 'DROP TABLE ?'

GO

1  DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
2
3  SET @Cursor = CURSOR FAST_FORWARD FOR
4
5  SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
6
7  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
8
9  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
10
11  OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
12
13  WHILE (@@FETCH_STATUS = 0)
14
15  BEGIN
16
17  Exec SP_EXECUTESQL @Sql
18
19  FETCH NEXT FROM @Cursor INTO @Sql
20
21  END
22
23  CLOSE @Cursor DEALLOCATE @Cursor
24
25  GO
26
27  EXEC sp_MSForEachTable 'DROP TABLE ?'
28
29  GO