242.SP_MSFOREACHTABLE

sp_MSforeachtable
这个系统存储过程有7个参数:
@command1 nvarchar(2000),			--第一条运行的T-SQL指令
@replacechar nchar(1) = N'?',		--指定的占位符号 
@command2 nvarchar(2000) = null,	--第二条运行的T-SQL指令
@command3 nvarchar(2000) = null,	--第三条运行的T-SQL指令
@whereand nvarchar(2000) = null,	--可选条件来选择表
@precommand nvarchar(2000) = null,	--在表前执行的指令
@postcommand nvarchar(2000) = null	--在表后执行的指令


使用示例:

--1.获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1="print '?'",
         @command2="sp_spaceused '?'",
         @command3= "SELECT count(*) FROM ? "

--2.更新PUBS数据库中已t开头的所有表的统计:
EXEC sp_MSforeachtable @whereand='and name like ''t%''',
         @replacechar='*',
         @precommand='print ''Updating Statistics.....''',
         @command1='print ''*'' update statistics * ',
         @postcommand= 'print ''Complete Update Statistics!'''


==========================================================================

建立自己的sp_MSforeachObject For zj:

USE MASTER
GO

CREATE proc sp_MSforeachObject
@objectType nvarchar(500)='U',	--对象类型,可以是下列对象类型中的一种或组合： 
								--C = CHECK 约束
								--D = 默认值或 DEFAULT 约束
								--F = FOREIGN KEY 约束
								--L = 日志
								--FN = 标量函数
								--IF = 内嵌表函数
								--P = 存储过程
								--PK = PRIMARY KEY 约束（类型是 K）
								--RF = 复制筛选存储过程
								--S = 系统表
								--TF = 表函数
								--TR = 触发器
								--U = 用户表
								--UQ = UNIQUE 约束（类型是 K）
								--V = 视图
								--X = 扩展存储过程
@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
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

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

/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
set @objectType=''''+replace(@objectType,',',''',''')+''''
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 o.xtype in('+@objectType+') '+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

===================================================================================

USE MASTER 
GO
SP_HELPTEXT sp_MSforeachtable

--下面时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

sp_MSforeachD B除了@whereand外,和 sp_MSforeachtable 的参数是一样的
我们可以通过这个存储过程检测所有的数据库,比如:

--2.检查所有的数据库
EXEC sp_MSforeachdb  @command1='print ''?''',
	@command2='DBCC CHECKDB (?) '

==============================================================================
建立自己的sp_MSforeachObject:

USE MASTER
GO

CREATE proc sp_MSforeachObject
@objectType int=1, 				--对象类型,可以是下列对象类型中的一种或组合： 
								--C = CHECK 约束
								--D = 默认值或 DEFAULT 约束
								--F = FOREIGN KEY 约束
								--L = 日志
								--FN = 标量函数
								--IF = 内嵌表函数
								--P = 存储过程
								--PK = PRIMARY KEY 约束（类型是 K）
								--RF = 复制筛选存储过程
								--S = 系统表
								--TF = 表函数
								--TR = 触发器
								--U = 用户表
								--UQ = UNIQUE 约束（类型是 K）
								--V = 视图
								--X = 扩展存储过程
@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
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

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

/* Defined  @isobject for save object type */
Declare @isobject varchar(256)

select @isobject= case @objectType when 1 then 'IsUserTable'
		when 2 then 'IsView'
		when 3 then 'IsTrigger'
		when 4 then 'IsProcedure' 
		when 5 then 'IsDefault'   
		when 6 then 'IsForeignKey'
		when 7 then 'IsScalarFunction'
		when 8 then 'IsInlineFunction'
		when 9 then 'IsPrimaryKey'
		when 10 then 'IsExtendedProc'    
		when 11 then 'IsReplProc'
		when 12 then 'IsRule'
	end

/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
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'''+@isobject+''') = 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

这样我们来测试一下:
1.获得所有的存储过程的脚本:
         EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
2.获得所有的视图的脚本:
         EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=2

3.比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO:
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4

  这样就非常方便的将每一个数据库对象改为DBO.

当然还要很多非常好的功能,大家可以自己深入研究吧:-)

 

 


 

 
 

对该文的评论 人气：137