--方法－：
-------------清空数据库-------------
/*
描述：
清空数据库
步骤：
清空外键表
清空对应外键表已经清空了的主表
版本：
时间 修改人 操作
变量：
@name varchar，表名
@sql varchar，动态sql语句
临时表：
#tmp 存放有主外键对应关系的表名
#table 存放对应外键表已清空、可以delete的表名
*/
1 --禁用触发器
2 exec sp_msforeachtable @command1 = "alter table ? disable trigger all"
3
4 go
5 ----清空外键表----
6
7 --更正 sysindexes 表的不正确内容
8 dbcc updateusage (0) with count_rows,no_infomsgs
9
10 declare @name sysname,@sql varchar(8000) set @sql = ''
11
12 --用游标组装sql语句删除表记录
13 declare t_cursor cursor for
14 select a.name
15 from sysobjects a join sysindexes b on a.id = b.id
16 where a.xtype='u' and b.indid < 2 and b.rows > 0 and a.id not in (select distinct rkeyid from sysreferences)
17 open t_cursor
18 fetch next from t_cursor into @name
19 while @@fetch_status = 0
20 begin
21 if len(@sql) + 10 + len(@name) > 8000
22 begin
23 exec (@sql)
24 set @sql = ''
25 end
26 set @sql = @sql + ' delete [' + @name + ']'
27 fetch next from t_cursor into @name
28 end
29 close t_cursor
30 deallocate t_cursor
31
32 exec (@sql)
33
34 dbcc updateusage (0) with count_rows,no_infomsgs
35
36 go
37
38 ----清空主表----
39
40 declare @sql varchar(8000) set @sql = ''
41 create table #table(rkeyid sysname)
42
43 select distinct _r.fkeyid,_r.rkeyid into #tmp
44 from sysreferences _r join sysindexes _i on _i.id = _r.rkeyid
45 where _i.indid < 2 and _i.rows > 0
46
47 while @@rowcount > 0
48 begin--清空对应外键表已经清空了的主表
49 insert #table
50 select distinct rkeyid
51 from #tmp _t
52 where exists (select 1 from sysindexes where id = _t.rkeyid and indid < 2 and rows > 0)
53 and not exists (
54 select 1 from #tmp _t2 join sysindexes _i on _i.id = _t2.fkeyid
55 where _i.indid < 2 and _i.rows > 0 and _t2.rkeyid = _t.rkeyid and _t2.fkeyid <> _t2.rkeyid
56 )
57
58 --这里@sql长度应该不会大于8000，不必用游标拆分字符串。
59 select @sql = @sql + ' delete [' + object_name(rkeyid) + ']' from #table
60
61 exec(@sql)
62
63 set @sql = ''
64
65 dbcc updateusage (0) with count_rows,no_infomsgs
66
67 delete #table
68 end
69
70 go
71
72 drop table #tmp,#table
73 go
74 --起用触发器
75 exec sp_msforeachtable @command1 = "alter table ? enable trigger all"
76
77 go
78
79 --查询
80 create table #table_count(table_name sysname,table_count int)
81
82 exec sp_msforeachtable "insert #table_count select '?',count(*) from ?"
83
84 select * from #table_count --where table_count > 0
85 order by table_count desc,table_name
86
87 drop table #table_count
88
89 go
90
91 ----------------------------------------------------------------------------
92
93 --方法二:
94
95 -------------清空数据库-------------
96
97 create proc p_delete_table(@table_id int)
98 as
99 /*
100 描述：
101 如果参数表是外键表，不做任何动作。
102 如果参数表是主表，则递归清空它周围的外键表，最后清空主表。
103 版本：
104 时间 修改人 操作
105 2007年05月09日 mengmou 创建
106 变量：
107 @table_id 表ID
108 @table 表变量，存放构成树形结构的表
109 */
110
111 begin
112 dbcc updateusage (0) with count_rows,no_infomsgs
113 declare @sql varchar(8000),@level int, @id int
114 select @sql = '',@level = 1
115 declare @table table (table_id int,level int)
116
117 if not exists(select 1 from sysreferences where fkeyid = @table_id and fkeyid <> rkeyid)
118 and exists(select 1 from sysindexes where id = @table_id and indid < 2 and rows > 0)
119 insert @table select @table_id,@level
120 else
121 return
122
123 while @@rowcount > 0
124 begin
125 set @level = @level + 1
126
127 insert @table
128 select distinct fkeyid,@level
129 from @table _t
130 join sysreferences _r on _r.rkeyid = _t.table_id
131 join sysindexes _i on _i.id = _r.fkeyid
132 where _r.fkeyid <> _r.rkeyid and _t.level = @level - 1 and _i.indid < 2 and _i.rows > 0
133 end
134
135 --@sql超过8000要用游标拆分字符串
136 declare t_cursor cursor for
137 select table_id from @table order by level desc
138 open t_cursor
139 fetch next from t_cursor into @id
140 while @@fetch_status = 0
141 begin
142 select @sql = 'delete [' + object_name(@id) + ']'
143 exec(@sql)
144 fetch next from t_cursor into @id
145 end
146 close t_cursor
147 deallocate t_cursor
148
149 end
150
151 go
152
153 --禁用触发器
154 exec sp_msforeachtable @command1 = "alter table ? disable trigger all"
155
156 go
157
158 exec sp_msforeachtable @command1 = "declare @id int select @id = object_id('?') exec p_delete_table @id"
159
160 go
161
162 --起用触发器
163 exec sp_msforeachtable @command1 = "alter table ? enable trigger all"
164
165 go
166
167 --查询
168
169 create table #table_count(table_name sysname,table_count int)
170
171 exec sp_msforeachtable "insert #table_count select '?',count(*) from ?"
172
173 select * from #table_count where table_count > 0 order by table_count desc,table_name
174
175 drop table #table_count
176
177 go
178
179 drop proc p_delete_table
日志出处:http://blog.csdn.net/mengmou/article/details/1626860
