MSSQL deletes all tables in the database

<pre name= "code" class= "HTML" > for this operation we can go first to understand the system table sysobjects (there is written on the blog), and drop, truncate,delete the difference.

------------Delete the foreign key constraint for all tables (drop) when deleting the data (Truncate;delete)-------DECLARE C1 cursor forselect ' ALTER TABLE [' + object_name (Parent_obj) + '] drop constraint [' +name+ ']; ' From sysobjectswhere xtype = ' F ' Open c1--create cursor declare @c1 varchar (8000) fetch next from C1 into @c1while (@ @fetch_status =0) b Eginexec (@c1) fetch next from C1 to @c1endclose c1deallocate C1--------------------Delete all tables-------------------------- Use database name Godeclare @sql varchar (8000) while (select COUNT (*) from sysobjects where type= ' U ') >0beginselect @sql = ' drop tab Le ' + namefrom sysobjectswhere (type = ' U ') ORDER by ' drop table ' + nameexec (@sql) End

<pre name= "code" class= "HTML" >    1. Truncate and delete only deletes data without deleting the structure of the table (definition) The    drop statement will delete the structure of the table that is dependent on the constraint (constrain), A trigger (trigger), index, or stored procedure/function that relies on the table will remain, but becomes invalid (invalid).       2. Truncate delete all records, reset the table (most obviously the automatic ID zero), delete the data will not be backed up; Delete delete record, automatic ID continue delect Delete will be a DDL language, like all other DDL languages, he will be implicitly committed, You cannot use the rollback command with truncate.     3. Be careful with drop and truncate, with delete and the Where condition, of course, customers that most of them will have backup three actually no priority, appropriate time with the appropriate method   # to delete some data rows with delete, note the WHERE clause .  # Want to delete the table, of course, with the drop      # want to keep the table and all the data deleted, if not related to the transaction, with truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.      # If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

MSSQL deletes all tables in the database