SQL Server : Drop or delete all tables from database - ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

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





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.


EmoticonEmoticon