SQL Server : Drop or delete all tables from database

In this article I am going to explain how to drop or delete all tables from SQL Server database.

In SQL Server, there is hidden store to delete all tables. Once you connect to SQL and execute this store procedure, it’s executed for each table.

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

If tables are referenced by foreign key constraints, in that case you have run this again and again to till all foreign key constraints delete.

You can run below given query to disable the foreign key constraints and after that execute the above given store procedure.

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))

begin

            declare @sql nvarchar(2000)

            SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME

            + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')

            FROM information_schema.table_constraints

            WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

            exec (@sql)

end