MSSQL中如何用SQL清除所有表的數據？這個需求分三種類型：
第一：只要數據庫中表是空的；
第二：表是空的，並且自增長列可以從１開始增長。
第三：表是空的，並且自增長列可以從１開始增長，而且存在表間的約束。
邀月稍微整理了下，放在這裡，便於有需要的朋友參閱。
其實，這不算什麼需求。只要用數據庫的生成腳本，幾分鐘即可生成一個干淨的表結構及存儲過程、視圖、約束等。這裡提供了另一種用SQL解決問題的方案。權當是無聊的學習，加深點印象吧。呵呵。
首先，作一些假設：假設database名為TestDB_2000_2005_2008
預先准備一些腳本
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
先來看看第一種需求： 只要數據庫中表是空的。
這個其實並不難，用一個游標循環得出所有表名，再清除所有表，delete或truncate table
提供幾個語句：以下語句均在SQL2000/SQL2005/SQL2008下使用通過。
方法甲：
/********************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
方法乙：
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
方法丙：
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)
再來看看第二種需求： 表是空的，並且自增長列可以從１開始增長 。
這種需求其實和第一種差不多。 因為我們在以上語句中使用的是 truncate table 語句，所以，表的自增長 列是默認從頭重新的。
關鍵是第三種需求： 表是空的，並且自增長列可以從１開始增長 ，而且存在表間的約束 。
這是個比較頭痛的問題。因為外鍵約束，不能使用truncate table語句，但是，如果使用delete，又不能使自增長列從１開始重排。
我們不妨先來增加一些約束條件：
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
此時再來執行甲乙丙語句時會提示：“無法截斷表 'd'，因為該表正由 FOREIGN KEY 約束引用。”
我們可以這樣設想：
1、先找出沒有外鍵約束的表，truncate
２、有外鍵的表，先delete,再復位identity列
於是得出，
語句丁（注意沒有使用游標 ）
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
小結：除了以上方法，還可以臨時禁用外鍵約束。語句為：
