Posts Tagged ‘SQL’

Delete all tables in SQL Server Database

Thursday, July 29th, 2010

This is out first big tip. This will save you loads of time if you want to delete all tables in a database. We consider here you have Microsoft SQL Server Management Studio installed on your machine.

Well the simple solution to delete all your tables in one go is:

1) You got to make sure there are no referential constraints otherwise your magic drop command won’t work.

2) Execute this simple command to list all your referential integrity commands.

select ‘alter table ‘ + OBJECT_NAME(parent_object_id) + ‘ drop constraint ‘ + name from sys.foreign_keys

3) Now simply copy all the commands listed in your results window.

4) Paste them in a new query and click execute.

5) Watch all your constraints deleted.

6) Now since, you deleted all the constraints time to drop the tables.

7) Execute this query

select ‘DROP TABLE ‘ + name from sys.tables

copy the results and paste it as a new query.

8) Voila! All your tables in your database are now deleted.

Isn’t that easy?

Please let us know.