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