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, --在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'' '功用类似
阅读更多
文章标签: SQL SQL Server 脚本
上一篇sp_MSforeachdb获得所有的数据库的存储空间
下一篇ORACLE SQL 索引(值得研读)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭