删除所表的所有数据

在开发的时候往往添加不少的测试数据，在执行脚本之前需要将原有的数据删除，而且还会有外键约束，标识列的问题。于是写了这个自动删除表的所有数据.

 1 IF OBJECT_ID('TEMP_CLEAR_ALL_DATA') IS NULL
 2 BEGIN
 3     DECLARE @tablename VARCHAR(50)
 4     --外键表信息
 5     SELECT
 6         OBJECT_NAME(parent_object_id) AS FTable,
 7         OBJECT_NAME(referenced_object_id) AS PTable
 8     INTO TEMP_CLEAR_ALL_DATA
 9     FROM sys.foreign_key_columns
10     --添加无外键引用的住建标的表
11     INSERT INTO TEMP_CLEAR_ALL_DATA
12     SELECT null, name FROM sys.tables WHERE name NOT IN 
13         (SELECT FTable FROM TEMP_CLEAR_ALL_DATA)
14         AND name <> 'TEMP_CLEAR_ALL_DATA'
15     --删除外键表记录
16     WHILE EXISTS(SELECT * FROM TEMP_CLEAR_ALL_DATA WHERE FTable IS NOT NULL)
17     BEGIN
18         DECLARE C CURSOR FOR 
19             SELECT DISTINCT FTable FROM TEMP_CLEAR_ALL_DATA WHERE FTable NOT IN
20                 (SELECT PTable FROM TEMP_CLEAR_ALL_DATA)
21         OPEN C
22         FETCH NEXT FROM c INTO @tablename
23             WHILE @@FETCH_STATUS = 0
24             BEGIN
25                 --删除数据
26                 EXEC('DELETE FROM [' + @tablename + ']')
27                 --回滚标识列
28                 IF EXISTS(SELECT * FROM sys.identity_columns WHERE [object_id] = OBJECT_ID(@tablename))
29                     EXEC('DBCC CHECKIDENT(['+ @tablename +'],RESEED,0)')
30                 ELSE 
31                     print @tablename + '没有标识列, 不需要回滚'
32                 FETCH NEXT FROM c INTO @tablename
33             END 
34         CLOSE C
35         DEALLOCATE c
36         DELETE FROM TEMP_CLEAR_ALL_DATA WHERE FTable NOT IN
37             (SELECT PTable FROM TEMP_CLEAR_ALL_DATA)
38     END
39     --删除剩余主键表记录(包含不带主键的表)
40     DECLARE C CURSOR FOR 
41         SELECT DISTINCT PTable FROM TEMP_CLEAR_ALL_DATA
42     OPEN C
43     FETCH NEXT FROM c INTO @tablename
44         WHILE @@FETCH_STATUS = 0
45         BEGIN
46             IF EXISTS(SELECT * FROM sys.identity_columns WHERE [object_id] = OBJECT_ID(@tablename))
47                 EXEC('DBCC CHECKIDENT(['+ @tablename +'],RESEED,0)')
48             ELSE 
49                 print @tablename + '没有标识列, 不需要回滚'
50             FETCH NEXT FROM c INTO @tablename
51         END 
52     CLOSE C
53     DEALLOCATE c
54     DELETE FROM TEMP_CLEAR_ALL_DATA
55     --删除临时表
56     DROP TABLE TEMP_CLEAR_ALL_DATA
57 END
58 GO

 

 

 

转载于:https://www.cnblogs.com/lailexin/articles/2759734.html