How can empty Database?

morik · February 17th, 2007, 11:27 AM

Hello
I want to know that how can empty DB?
first , Drop all constraint (primary Key & foreign key) then DELETE all
Record !
Thanks

SQLScott · February 17th, 2007, 11:48 AM

This could be fairly time consuming based on the number of tables you have.

First, you need to remove all foreign key constraints. Once your foreign key constraints are removed, you can delete all the rows from the tables. So, it would be something like this:

ALTER TABLE tablename DROP CONSTRAINT constraintname
GO

DELETE FROM tablename
GO

You don't need to drop the Primary key to delete rows from a table.

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

morik · February 18th, 2007, 05:52 AM

Hi scott
I want to perform this action Automatic by "Batch command" .
for example (cursor , Loop , ...)
first , identify all reference Top-Down
then DELETE all of Records Down-Top .
Thank you very much .

SQLScott · February 18th, 2007, 10:32 AM

Ah, ok, no problem. I'll put something together and send it to you shortly..

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

SQLScott · February 18th, 2007, 04:45 PM

So, i think your best bet is to use the system tables and cursors.

Within a cursor, you can query the system tables and for each table, query the system tables to identify all the foreign keys and remove them. Then, delete the data.

This is off the top of my head and have not tested this, but i think it would work. Does this make sense?

If you want an example, i can throw a psuedo-code example together...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

morik · February 19th, 2007, 08:36 AM

Hello Scott
I solve this conundrum .
thank to expend your time for help me .

Jeff Moden · February 19th, 2007, 09:12 AM

Morik,

How did you solve it, please?

--Jeff Moden

morik · February 25th, 2007, 05:02 AM

Jeff
sorry for delay .
contact me with ([email protected]),please .

robprell · March 2nd, 2007, 01:56 PM

Another possibly easier route is to query the system tables and create a script that will create all tables without any records or constraints. Then drop the database, re-create the database and apply the script that creates all tables.

morik · March 6th, 2007, 02:07 PM

-- Persian Boy
Hello
do you really think that a DATABASE only made of TABLE?????????????
convenience !