转自:http://nicedayyep.iteye.com/blog/674977
昨天对一测试库要删除所有记录后生成一个备份,本来想自己写个存储过程,想法是从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这个表就没有了,变成了真正的系统表了。