(SqlServer)不公开存储过程sp_Msforeachtable与sp_Msforeachdb详

发布时间：2020-12-12 14:44:41 所属栏目：MsSql教程来源：网络整理

导读：? 【推荐】 (SqlServer) 不公开存储过程 sp_Msforeachtable 与 sp_Msforeachdb 详解 ——通过知识共享树立个人品牌。 ? 一.简要介绍: 系统存储过程 sp_MSforeachtable 和 sp_MSforeachdb , 是微软提供的两个不公开的存储过程。从mssql6.5开始，存放在SQL S

【推荐】(SqlServer)不公开存储过程

sp_Msforeachtable与sp_Msforeachdb详解

——通过知识共享树立个人品牌。

一.简要介绍:

系统存储过程sp_MSforeachtable和sp_MSforeachdb,是微软提供的两个不公开的存储过程。从mssql6.5开始，存放在SQL Server的MASTER数据库中。可以用来对某个数据库的所有表或某个SQL服务器上的所有数据库进行管理，下面将对此进行详细介绍。

作为数据库管理者或开发者等经常会检查整个数据库或用户表。

如:检查整个数据库的容量，看指定数据库所有用户表的容量,所有表的记录数等等，我们一般处理这样的问题都是通过游标来达到要求。

如果我们用sp_MSforeachtable就可以非常方便的达到相同的目的，

如：sql查询所有用户表的列表，详细信息，如：记录数，表占用大小等

EXEC?sp_MSforeachtable?" EXECUTE?sp_spaceused? ' ? '"

二.各参数说明:

?? @command1? nvarchar( 2000),?????????? -- 第一条运行的SQL指令
?? @replacechar? nchar( 1)? =?N ' ? ',?????? -- 指定的占位符号
?? @command2? nvarchar( 2000) =? null,???? -- 第二条运行的SQL指令
?? @command3? nvarchar( 2000)?? =? null,?? -- 第三条运行的SQL指令
?? @whereand? nvarchar( 2000)?? =? null,?? -- 可选条件来选择表
?? @precommand? nvarchar( 2000) =? null,?? -- 执行指令前的操作(类似控件的触发前的操作)
?? @postcommand? nvarchar( 2000) =? null?? -- 执行指令后的操作(类似控件的触发后的操作)

?以后为sp_MSforeachtable的参数，sp_MSforeachdb不包括参数@whereand

?我们在master数据库里执行下面的语句可以看到两个proc详细的代码

use?master
exec?sp_helptext?sp_MSforeachtable
exec?sp_helptext?sp_Msforeachdb

三、使用举例:

???--统计数据库里每个表的详细情况：

?? exec?sp_MSforeachtable? @command1 ="sp_spaceused? ' ? '"
?
?? -- 获得每个表的记录数和容量:
?? EXEC?sp_MSforeachtable? @command1 =" print? ' ? '",
????????????????????????? @command2 ="sp_spaceused? ' ? '",
????????????????????????? @command3 =?" SELECT? count( *)? FROM???"
?
?? -- 获得所有的数据库的存储空间:
?? EXEC?sp_MSforeachdb? @command1 =" print? ' ? '",
?????????????????????? @command2 ="sp_spaceused?"
?
?? -- 检查所有的数据库
?? EXEC?sp_MSforeachdb? @command1 =" print? ' ? '",
?????????????????????? @command2 =" DBCC?CHECKDB?(?)?"
?
?? -- 更新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! '"
?
?? -- 删除当前数据库所有表中的数据
??sp_MSforeachtable? @command1 = ' Delete?from?? '
??sp_MSforeachtable? @command1? =?" TRUNCATE? TABLE??"
?
-- 查询数据库所有表的记录总数
CREATE? TABLE?# temp?(TableName? VARCHAR?( 255),?RowCnt? INT)
EXEC?sp_MSforeachtable? ' INSERT?INTO?#temp?SELECT? '' ? '' ,?COUNT(*)?FROM?? '
SELECT?TableName,?RowCnt? FROM?# temp? ORDER? BY?TableName
DROP? TABLE?# temp
?
-- 检查数据库里每个表或索引视图的数据、索引及text、ntext?和image?页的完整性
-- 下列语句需在单用户模式下执行（sp_dboption?'db_name',?'single?user',?'true'）
-- ,将true改成false就又变成多用户了
exec?sp_msforeachtable?" dbcc?checktable( ' ? ',repair_rebuild)"

4.参数@whereand的用法：

?@whereand参数在存储过程中起到指令条件限制的作用，具体的写法如下：

?@whereend,可以这么写

@whereand = ' ?AND?o.name?in?( '' Table1 '' , '' Table2 '' ,.......) '?

又如：

我想更新Table1/Table2/Table3中NOTE列为NULL的值

?sp_MSforeachtable? @command1 = ' Update???Set?NOTE= '''' ?Where?NOTE?is?NULL '
? ? ? ? ? ? ? ? ?,@whereand = ' ?AND?o.name?in?( '' Table1 '' , '' Table3 '' ) '

5. "?"特别说明：

"?"在存储过程的特殊用法,造就了这两个功能强大的存储过程.

? "?"的作用,相当于DOS命令中和我们在WINDOWS下搜索文件时的通配符的作用。

6.小结

?有上面的分析,我们可以建立自己的sp_MSforeachObject:（注：下面的内容来源于网上。）

?USE?MASTER

GO
CREATE? proc?sp_MSforeachObject
? @objectType? int = 1,
? @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)
? /* ?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 '' ]] '' )?+? '' ] '' ?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

我们来测试一下:

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

? 2011??EricHu

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong?

作者：EricHu（DB、CS、BS、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704?? E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看?[置顶]索引贴——（不断更新中）

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!