mercoledì 18 aprile 2012 18:44IS 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.
Tutte le risposte
mercoledì 18 aprile 2012 19:58
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
giovedì 19 aprile 2012 12:12
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?
giovedì 19 aprile 2012 13:31
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.
lunedì 23 aprile 2012 12:46
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.
mercoledì 25 aprile 2012 11:45
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)