Single Query to DROP SQL DB

Question

IS there a single SQL to drop all tables in a database having various relationships? Currently I am doing one at a time using DROP TABLE XYZ once each time depending on the FK and PK relations.

Answer 1

pls try

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Can you clarify your requirement in more detail?

Do you wnat to drop all the tables of the database or you want drop only those tables where ever any FK and PK present?

SQLCommitted.com

Answer 3

I need all the tables to be dropped in a DB. But don't want to go with dropping the whole DB in a single shot as that will delete the user level permissions too.

Answer 4

You need to drop foreign keys before you can drop the tables:

To drop all foreign keys and tables you can use below code:

SELECT	'ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) + ' DROP CONSTRAINT ' + name
FROM	sys.foreign_keys
UNION ALL
SELECT	'DROP TABLE ' + SCHEMA_NAME(schema_id) + '.' + name
FROM	sys.tables
WHERE object_id IN (SELECT parent_object_id FROM sys.foreign_keys)

This removes only tables which have foreign keys. To drop all tables in database remove WHERE clause.

---

- Vishal

SqlAndMe.com

Answer 5

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Run the first commmand which will disable all your constarints . This is required to disable all foreigns keys.

Then Run the second command which will drop all your tables.

Sandip Pani (MCTS, MCITP)

SQLCommitted