/*********************************************************************************
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, --在sp_MSforeach_worker前执行的指令
@postcommand nvarchar(2000) = null --在sp_MSforeach_worker后执行的指令
************************************************************************************/
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
Useage:
exec sp_msforeachtable @command1="print '?' dbcc checktable('?')"
上面的语句也可以这样写:
exec sp_msforeachtable @command1="print '?'",
@command2= "dbcc checktable('?')"
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.....' print ''",
@command1="print '*' update statistics * ",
@postcommand= "print''print 'complete update statistics!'"
3.批量更改SQL Server数据库中的用户对象
exec sp_msforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '
4。获得所有的存储过程的脚本：
EXEc sp_MSforeachObject @command1=“sp_helptext '?' “，@objectType=4
5。获得所有的视图的脚本：
EXEc sp_MSforeachObject @command1=“sp_helptext '?' “，@objectType=2
6。比如在开发过程中，没一个用户都是自己的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。
与exec sp_msforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' ' 功用类似
发表于 @ 2008年06月06日 10:16:15|评论(loading...)|编辑|收藏