删除所有表的所有记录（MSSQL）

昨天对一测试库要删除所有记录后生成一个备份，本来想自己写个存储过程，想法是从sysobjects表中查出所有用户表，挨个truncate table，实现时发现这样做只能用游标，在网上查了下，MSSQL本身就有这样的游标查询，叫“sp_MSForEachTable ”

CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO

上面这个就搞定了。

稍微分析下sp_MSForEachTable 这个存储过程，

create proc sp_MSforeachtable
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */

/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
   exec(@precommand)

/* Create the select */
   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
   exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)
   exec(@postcommand)

return @retval

GO

重点在于定义了一个cursor为hcForEach，然后把set给sp_MSforeach_worker 就行了，而这个存储过程查询条件也很有意思，本来我认为应该以xtype以条件，看过后才知道人家用了OBJECTPROPERTY函数，意义上没变化， 不过程序读起来感到优雅和简单，这就是差距啊。

还有一点要说明的它的另一个条件category & 0x0002 =0，看了下联机帮助说是

 

category

int

用于发布、约束和标识。

在网上google下也没找到有用的信息，不过自己看了下，这个是用二进制做标志位的，第二位如果是1的话就是系统的东西，比如说在2000中存关 系图的dtproperties表就是系统表，但是用OBJECTPROPERTY(o.id, 'IsUserTable') = 1且能把这个表查出来，所以加上category这个条件才能更严谨些。2005这个表就没有了，变成了真正的系统表了。