有朋友问起：MSSQL中如何用SQL清除所有表的数据？这个需求分三种类型：
第一：只要数据库中表是空的；
第二：表是空的，并且自增长列可以从１开始增长。
第三：表是空的，并且自增长列可以从１开始增长，而且存在表间的约束。
邀月稍微整理了下，放在这里，便于有需要的朋友参阅。
其实，这不算什么需求。只要用数据库的生成脚本，几分钟即可生成一个干净的表结构及存储过程、视图、约束等。这里提供了另一种用SQL解决问题的方案。权当是无聊的学习，加深点印象吧。呵呵。
首先，作一些假设：假设database名为TestDB_2000_2005_2008
预先准备一些脚本
view plaincopy to clipboardprint?
1. use master
2. go
3. IF OBJECT_ID('TestDB_2000_2005_2008') IS NOT NULL
4. -- print 'Exist databse!'
5. -- else print 'OK!'
6. DROP Database TestDB_2000_2005_2008
7. GO
8. Create database TestDB_2000_2005_2008
9. go
10. use TestDB_2000_2005_2008
11. go
12. IF OBJECT_ID('b') IS NOT NULL
13. drop table b
14. go
15. create table b(id int identity(1,1),ba int,bb int)
16. --truncate table b
17. insert into b
18. select 1,1 union all
19. select 2,2 union all
20. select 1,1
21. IF OBJECT_ID('c') IS NOT NULL
22. drop table c
23. go
24. create table c(id int identity(1,1),ca int,cb int)
25. insert into c
26. select 1,2 union all
27. select 1,3
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下使用通过。
方法甲：
+ expand sourceview plaincopy to clipboardprint?
1. /********************MSSQL 2000/2005/2008***********************/
2. use TestDB_2000_2005_2008
3. go
4. select * from b
5. select * from c
6. Declare @t varchar (1024)
7. Declare @SQL varchar(2048)
8. Declare tbl_cur cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
9. OPEN tbl_cur FETCH NEXT from tbl_cur INTO @t
10. WHILE @@FETCH_STATUS = 0
11. BEGIN
12. SET @SQL='TRUNCATE TABLE '+ @t
13. --print (@SQL)
14. EXEC (@SQL)
15. FETCH NEXT from tbl_cur INTO @t
16. END
17. CLOSE tbl_cur
18. DEALLOCATE tbl_Cur
19. select * from b
20. select * from c
/********************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
方法乙：
+ expand sourceview plaincopy to clipboardprint?
1. /********************MSSQL 2000/2005/2008***********************/
2. use TestDB_2000_2005_2008
3. go
4. select * from b
5. select * from c
6. select * from d
7. select * from e
8. DECLARE @TableName VARCHAR(256)
9. DECLARE @varSQL VARCHAR(512)
10. DECLARE @getTBName CURSOR SET @getTBName = CURSOR FOR SELECT name FROM sys.Tables WHERE NAME NOT LIKE 'Category'
11. OPEN @getTBName FETCH NEXT FROM @getTBName INTO @TableName
12. WHILE @@FETCH_STATUS = 0
13. BEGIN
14. SET @varSQL = 'Truncate table '+ @TableName
15. --PRINT (@varSQL)
16. EXEC (@varSQL)
17. FETCH NEXT FROM @getTBName INTO @TableName
18. END
19. CLOSE @getTBName
20. DEALLOCATE @getTBName
21. ----select * from b
22. ----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
方法丙：
+ expand sourceview plaincopy to clipboardprint?
1. Declare @t table(query varchar(2000),tables varchar(100))
2. Insert into @t
3. select 'Truncate table ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T
4. left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
5. on T.table_name=TC.table_name
6. where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and
7. T.table_name not in ('dtproperties','sysconstraints','syssegments') and
8. Table_type='BASE TABLE'
9. Insert into @t
10. select 'delete from ['+T.table_name+']', T.Table_Name from INFORMATION_SCHEMA.TABLES T
11. left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
12. on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
13. Declare @sql varchar(8000)
14. Select @sql=IsNull(@sql+' ','')+ query from @t
15. print(@sql)
16. Exec(@sql)
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，又不能使自增长列从１开始重排。
我们不妨先来增加一些约束条件：
+ expand sourceview plaincopy to clipboardprint?
1. CREATE TABLE [d] (
2. [id] [int] IDENTITY (1, 1) NOT NULL ,
3. [da] [int] NULL ,
4. [db] [int] NULL ,
5. CONSTRAINT [PK_d] PRIMARY KEY CLUSTERED
6. (
7. [id]
8. ) ON [PRIMARY]
9. ) ON [PRIMARY]
10. CREATE TABLE [e] (
11. [id] [int] IDENTITY (1, 1) NOT NULL ,
12. [da] [int] NULL ,
13. [db] [int] NULL ,
14. [did] [int] NULL ,
15. CONSTRAINT [FK_e_d] FOREIGN KEY
16. (
17. [did]
18. ) REFERENCES [d] (
19. [id]
20. )
21. ) ON [PRIMARY]
22. insert into d
23. select 5,6 union all
24. select 7,8 union all
25. select 9,9
26. insert into e
27. select 8,6,1 union all
28. select 8,8,2 union all
29. select 8,9,2
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列
于是得出，
语句丁（注意没有使用游标 ）
+ expand sourceview plaincopy to clipboardprint?
1. SET NoCount ON
2. DECLARE @tableName varchar(512)
3. Declare @SQL varchar(2048)
4. SET @tableName=''
5. WHILE EXISTS
6. (
7. --Find all child tables and those which have no relations
8. SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T
9. LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name = TC.table_name
10. WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
11. AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )
12. AND Table_type = 'BASE TABLE'
13. AND T.table_name > @TableName
14. )
15. Begin
16. SELECT @tableName = min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T
17. LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name=TC.table_name
18. WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
19. AND T.table_name NOT IN ( 'dtproperties', 'sysconstraints', 'syssegments' )
20. AND Table_type = 'BASE TABLE'
21. AND T.table_name > @TableName
22. --Truncate the table
23. SET @SQL = 'Truncate table '+ @TableName
24. print (@SQL)
25. Exec(@SQL)
26. End
27.
28. SET @TableName=''
29. WHILE EXISTS
30. (
31. --Find all Parent tables
32. SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T
33. LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name = TC.table_name
34. WHERE TC.constraint_Type = 'Primary Key'
35. AND T.table_name <> 'dtproperties'
36. AND Table_type='BASE TABLE'
37. AND T.table_name > @TableName
38. )
39. Begin
40. SELECT @tableName = min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T
41. LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name=TC.table_name
42. WHERE TC.constraint_Type = 'Primary Key'
43. AND T.table_name <> 'dtproperties'
44. AND Table_type = 'BASE TABLE'
45. AND T.table_name > @TableName
46. --Delete the table
47.
48. SET @SQL = ' delete from '+ @TableName
49. print (@SQL)
50. Exec(@SQL)
51. --Reset identity column
52. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
53. WHERE COLUMNPROPERTY(
54. OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),
55. column_name,'IsIdentity'
56. ) = 1
57. )
58. DBCC CHECKIDENT(@tableName,RESEED,0)
59. End
60. SET NoCount OFF
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
小结：除了以上方法，还可以临时禁用外键约束。语句为：
view plaincopy to clipboardprint?
1. -- --禁用所有约束
2. --exec sp_msforeachtable 'alter table ? nocheck CONSTRAINT all'
3. -- --再启用所有外键约束
4. --exec sp_msforeachtable 'alter table ? check constraint all'
分享到：
相关推荐
`TOP`子句在SQL查询中用于限制返回的数据行数，通常与`ORDER BY`一起使用，以确定返回哪些行。 在原始的存储过程`getWorkPlan2`中，`TOP 5`用于获取`worklist`表中`lngExecHumanID`等于指定用户ID的最新5项工作计划...
SQL SERVER在线管理源码修正版是一个针对SQL Server数据库进行远程管理的工具，它基于开源项目进行了一定的改进，使得用户能够更加方便地对SQL Server数据库进行操作。这个工具主要适用于那些需要远程维护多个SQL ...
下面我们将详细探讨SQL Server在线管理的关键知识点以及压缩包中的文件可能对应的功能。 1. SQL Server概述：SQL Server是由Microsoft开发的关系型数据库管理系统，广泛应用于企业级数据存储和管理。它支持T-SQL...
通过以上步骤，你应该能够成功地在同一个开发环境中同时安装并使用VS2005 Team System和SQL Server 2005 Enterprise Edition。如果在安装过程中遇到任何问题，记得查阅微软官方文档或在线社区，获取更多的技术支持和...
为了防止Excel进程占用过多资源或出现“僵尸”进程，代码中使用了计时器并在每次计时结束时调用`Cleanup`函数，该函数会清除计时器并尝试进行垃圾回收。 #### 四、注意事项 - 由于安全性和兼容性原因，使用ActiveX...
通过这个示例项目，学习者可以了解到如何在SSIS中配置和使用CDC，包括设置数据库的CDC功能、创建和管理CDC实例、设计SSIS包来处理变更数据，以及如何将这些变更有效地集成到数据仓库中。这将帮助数据管理员和ETL...
《Microsoft SQL Server 2008 Analysis Services Step by Step》随书数据库，由邀月(3w@live.cn)整理上传，本数据库只限学习、研究之用，不得用于商业用途，否则后果自负。
ASP.NET 中获取远程网页内容的一些方法 在ASP.NET中获取远程网页的内容是非常常见的需求，本文将介绍两种方法来实现这个需求。 一、本机直接上网时 在ASP.NET中，可以使用HttpWebRequest和HttpWebResponse来获取...
### 获取远程网页的内容之二（downmoon原创） #### 概述 本文主要介绍了在特定代理环境下，如何通过编程方式获取远程网页的内容。该方法适用于那些需要通过代理服务器访问互联网的企业或机构环境。文章中提供了...
一个相当独立的....支持 DataGrid 及 Repeater分页使用方法：新建两个解决方案 一为Pager，一为webtest 下载后请将两个解决方案合并一下，即新的解决方案包括两个项目 Pager WebTest 详细介绍看以下链接 ...
在Windows Forms（Winform）开发环境中，为了处理大量数据并提供良好的用户体验，通常会使用分页控件。分页控件允许用户逐步浏览数据，而不是一次性加载所有内容，这不仅提高了程序性能，还降低了内存占用。本文将...
在jQuery实现的示例中，开发者可能使用AJAX的$.ajax或$.getJSON方法发送异步请求，获取数据后使用jQuery的DOM操作函数动态更新下拉列表。这种做法使得代码更加简洁且执行效率高。 再者，ExtJS是一个全面的...
获取远程网页的内容之一（downmoon原创） 一、本机直接上网时： 获取指定远程网页内容#region 获取指定远程网页内容 代码如下: /**//// /// 获取指定远程网页内容 /// /// ”strUrl”>所要查找的远程...
在软件开发中，版本控制系统是确保代码质量和协作开发的重要工具。Visual SourceSafe（VSS）是一个曾经广泛使用的版本控制系统，它为开发者提供了一种存储、管理和跟踪源代码文件变更的解决方案。本内容主要讨论的是...
- **性能考虑**：对于大规模数据抓取，应考虑使用异步方法提高效率，并合理设置请求间隔避免对服务器造成过大压力。 以上是关于 ASP.NET 下获取远程网页内容的相关知识点的详细介绍。通过学习这些内容，开发者可以...