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'
