(转)MSSQL中如何用SQL清除所有表的数据(downmoon)?
生活随笔
收集整理的這篇文章主要介紹了
(转)MSSQL中如何用SQL清除所有表的数据(downmoon)?
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
有朋友問起:MSSQL中如何用SQL清除所有表的數據?這個需求分三種類型:
第一:只要數據庫中表是空的;
第二:表是空的,并且自增長列可以從1開始增長。
第三:表是空的,并且自增長列可以從1開始增長,而且存在表間的約束。
邀月稍微整理了下,放在這里,便于有需要的朋友參閱。
其實,這不算什么需求。只要用數據庫的生成腳本,幾分鐘即可生成一個干凈的表結構及存儲過程、視圖、約束等。這里提供了另一種用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?
方法乙:
/********************MSSQL?2000/2005/2008***********************/
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)
再來看看第二種需求:表是空的,并且自增長列可以從1開始增長。
這種需求其實和第一種差不多。因為我們在以上語句中使用的是truncate table語句,所以,表的自增長列是默認從頭重新的。
關鍵是第三種需求:表是空的,并且自增長列可以從1開始增長,而且存在表間的約束。
這是個比較頭痛的問題。因為外鍵約束,不能使用truncate table語句,但是,如果使用delete,又不能使自增長列從1開始重排。
我們不妨先來增加一些約束條件:
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
2、有外鍵的表,先delete,再復位identity列
于是得出,
語句丁(注意沒有使用游標)
?SET?NoCount?ON
???DECLARE?@tableName?varchar(512)
???Declare?@SQL?varchar(2048)
???SET?@tableName=''
???WHILE NOT 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
小結:除了以上方法,還可以臨時禁用外鍵約束。語句為:
--?--禁用所有約束
--exec?sp_msforeachtable?'alter?table???nocheck?CONSTRAINT?all'
--?--再啟用所有外鍵約束
--exec?sp_msforeachtable?'alter?table???check?constraint?all'
第一:只要數據庫中表是空的;
第二:表是空的,并且自增長列可以從1開始增長。
第三:表是空的,并且自增長列可以從1開始增長,而且存在表間的約束。
邀月稍微整理了下,放在這里,便于有需要的朋友參閱。
其實,這不算什么需求。只要用數據庫的生成腳本,幾分鐘即可生成一個干凈的表結構及存儲過程、視圖、約束等。這里提供了另一種用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?
方法乙:
/********************MSSQL?2000/2005/2008***********************/
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)
再來看看第二種需求:表是空的,并且自增長列可以從1開始增長。
這種需求其實和第一種差不多。因為我們在以上語句中使用的是truncate table語句,所以,表的自增長列是默認從頭重新的。
關鍵是第三種需求:表是空的,并且自增長列可以從1開始增長,而且存在表間的約束。
這是個比較頭痛的問題。因為外鍵約束,不能使用truncate table語句,但是,如果使用delete,又不能使自增長列從1開始重排。
我們不妨先來增加一些約束條件:
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
2、有外鍵的表,先delete,再復位identity列
于是得出,
語句丁(注意沒有使用游標)
?SET?NoCount?ON
???DECLARE?@tableName?varchar(512)
???Declare?@SQL?varchar(2048)
???SET?@tableName=''
???WHILE NOT 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
小結:除了以上方法,還可以臨時禁用外鍵約束。語句為:
--?--禁用所有約束
--exec?sp_msforeachtable?'alter?table???nocheck?CONSTRAINT?all'
--?--再啟用所有外鍵約束
--exec?sp_msforeachtable?'alter?table???check?constraint?all'
另外一種:
?
?
?
?
轉載于:https://www.cnblogs.com/s021368/articles/1640316.html
總結
以上是生活随笔為你收集整理的(转)MSSQL中如何用SQL清除所有表的数据(downmoon)?的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 入围51CTO 2009年度“最受读者欢
- 下一篇: 放手天下