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'))
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
What do you think about this article?
If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.