In MSSQL, how does one use SQL to clear data from all tables? There are three types of requirements:
First, as long as the table in the database is empty;
Second, the table is empty and the auto-growth column can grow from 1.
Third: The table is empty, and the auto-growth column can grow from 1, and there are constraints between tables.
The invitation month has been sorted out and put here for reference by friends who need it.
In fact, this is not a requirement. You only need to use the script generated by the database to generate a clean table structure, stored procedures, views, and constraints in just a few minutes. Here we provide another solution to solve the problem using SQL. The right course is boring, so let's get a deeper impression. Haha.
First, make some assumptions: assume that the database name isTestdb_2000_2005_2008
Prepare some scripts in advance
Use master Go If object_id ('testdb _ 2000_2005_2008 ') is not null -- Print 'exist Databse! ' -- Else print 'OK! ' Drop database testdb_2000_2005_2008 Go Create Database testdb_2000_2005_2008 Go Use testdb_2000_2005_2008 Go If object_id ('B') is not null Drop Table B Go Create Table B (ID int identity (1, 1), BA int, BB INT) -- Truncate Table B Insert into B Select 1, 1, Union all Select 2, 2 Union all Select 1, 1 If object_id ('C') is not null Drop Table C Go Create Table C (ID int identity (1, 1), Ca int, cb int) Insert into C Select 1, 2 Union all Select 1, 3 |
Let's take a look at the first requirement:
As long as the table in the database is empty.
This is actually not difficult. Use a cursor to cyclically obtain all table names, and then clear all tables, delete or truncate tables.
The following statements are used in SQL2000, sql2005, and sql2008.
Method:
/******************* MSSQL 2000/2005/2008 *************** ********/ Use testdb_2000_2005_2008 Go Select * from B Select * from C Declare @ t varchar (1024) Declare @ SQL varchar (2048) Declare tbl_cur cursor for select table_name from information_schema.tables where table_type = 'base table' Open tbl_cur fetch next from tbl_cur into @ t While @ fetch_status = 0 Begin Set @ SQL = 'truncate table' + @ t -- Print (@ SQL) Exec (@ SQL) Fetch next from tbl_cur into @ t End Close tbl_cur Deallocate tbl_cur Select * from B Select * from C |
Method B:
Use testdb_2000_2005_2008 Go Select * from B Select * from C Select * from D Select * from E Declare @ tablename varchar (256) Declare @ varsql varchar (512) Declare @ gettbname cursor set @ gettbname = cursor for select name from SYS. tables where name not like 'category' Open @ gettbname fetch next from @ gettbname into @ tablename While @ fetch_status = 0 Begin Set @ varsql = 'truncate table' + @ tablename -- Print (@ varsql) Exec (@ varsql) Fetch next from @ gettbname into @ tablename End Close @ gettbname Deallocate @ gettbname ---- Select * from B ---- Select * from C |
Method C:
Declare @ t table (query varchar (2000), tables varchar (100 )) Insert into @ t Select 'truncate table ['+ T. table_name +'] ', T. table_name from information_schema.tables t Left Outer Join information_schema.table_constraints TC On T. table_name = tc. table_name Where (TC. constraint_type = 'foreign key' or TC. constraint_type is null) and T. table_name not in ('dtproperties', 'sysconstraints', 'syssegments ') and Table_type = 'base table' Insert into @ t Select 'delete from ['+ T. table_name +'] ', T. table_name from information_schema.tables t Left Outer Join information_schema.table_constraints TC On T. table_name = tc. table_name where TC. constraint_type = 'Primary key' and T. table_name <> 'Dtproperties' and table_type = 'base table' Declare @ SQL varchar (8000) Select @ SQL = isnull (@ SQL + '','') + query from @ t Print (@ SQL) Exec (@ SQL) |
Let's take a look at the second requirement:
The table is empty and the auto-growth column can grow from 1.
.
This requirement is similar to the first one.
Because we use
Truncate table
Statement.
Columns are re-created by default.
The key is the third requirement:
The table is empty and the auto-growth column can grow from 1.
And there are constraints between tables.
.
This is a headache. The truncate TABLE statement cannot be used because of foreign key constraints. However, if Delete is used, auto-incrementing Columns cannot be rearranged from 1.
We may add some constraints first:
Create Table [d] ( [ID] [int] identity (1, 1) not null, [Da] [int] Null, [DB] [int] Null, Constraint [pk_d] primary key clustered ( [ID] ) On [primary] ) On [primary] Create Table [e] ( [ID] [int] identity (1, 1) not null, [Da] [int] Null, [DB] [int] Null, [Did] [int] Null, Constraint [fk_e_d] foreign key ( [Did] ) References [d] ( [ID] ) ) On [primary] Insert into D Select 5, 6 Union all Select 7, 8 Union all Select 9, 9 INSERT INTO E Select 8, 6, 1 Union all Select 8, 8, 2 Union all Select 8, 9, 2 |
When you execute the Statement, the following message is displayed:"The table 'D' cannot be truncated because it is being referenced by the foreign key constraint ."
We can imagine this:
1. First, find the table without foreign key constraints, truncate
2. For tables with foreign keys, delete the table first and then reset the identity column.
As a result,
Statement (Note that no cursor is used
)
Set nocount on Declare @ tablename varchar (512) Declare @ SQL varchar (2048) Set @ tablename ='' While exists ( -- Find all child tables and those which have no relations Select T. table_name from information_schema.tables t Left Outer Join information_schema.table_constraints TC on T. table_name = tc. table_name Where (TC. constraint_type = 'foreign key' or TC. constraint_type is null) And T. table_name not in ('dtproperties', 'sysconstraints', 'syssegments ') And table_type = 'base table' And T. table_name> @ tablename ) Begin Select @ tablename = min (T. table_name) from information_schema.tables t Left Outer Join information_schema.table_constraints TC on T. table_name = tc. table_name Where (TC. constraint_type = 'foreign key' or TC. constraint_type is null) And T. table_name not in ('dtproperties', 'sysconstraints', 'syssegments ') And table_type = 'base table' And T. table_name> @ tablename -- Truncate the table Set @ SQL = 'truncate table' + @ tablename Print (@ SQL) Exec (@ SQL) End
Set @ tablename ='' While exists ( -- Find all parent tables Select T. table_name from information_schema.tables t Left Outer Join information_schema.table_constraints TC on T. table_name = tc. table_name Where TC. constraint_type = 'Primary key' And T. table_name <> 'dtproperties' And table_type = 'base table' And T. table_name> @ tablename ) Begin Select @ tablename = min (T. table_name) from information_schema.tables t Left Outer Join information_schema.table_constraints TC on T. table_name = tc. table_name Where TC. constraint_type = 'Primary key' And T. table_name <> 'dtproperties' And table_type = 'base table' And T. table_name> @ tablename -- Delete the table
Set @ SQL = 'delete from' + @ tablename Print (@ SQL) Exec (@ SQL) -- Reset identity column If exists (select * From information_schema.columns Where columnproperty ( Object_id (quotename (table_schema) + '.' + quotename (@ tablename )), Column_name, 'isidentity' ) = 1 ) DBCC checkident (@ tablename, reseed, 0) End Set nocount off |
Summary: In addition to the preceding methods, you can temporarily disable foreign key constraints. Statement:
- -- Disable all constraints
- -- Exec sp_msforeachtable
'Alter table? Nocheck constraint all'
- -- Enable all foreign key constraints again
- -- Exec sp_msforeachtable
'Alter table? Check constraint all'