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

发布时间：2020-12-12 14:46:54 所属栏目：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)?? 第三条运行的SQL指令 @whereand? 可选条件来选择表 @precommand? 执行指令前的操作(类似控件的触发前的操作) @postcommand? 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? =" print? '",
????????????????????????? @command2 @command3 =?" SELECT? count( *)? FROM???"
?
?? 获得所有的数据库的存储空间: EXEC?sp_MSforeachdb? ="sp_spaceused?"
?
?? 检查所有的数据库 DBCC?CHECKDB?(?)?"
?
?? 更新PUBS数据库中已t开头的所有表的统计: EXEC?sp_MSforeachtable??? @whereand =" and?name? like? t% @replacechar = * @precommand Updating?Statistics..... '? ''",255)">update? statistics? *?",0)">@postcommand print '' Complete?Update?Statistics! 删除当前数据库所有表中的数据
??sp_MSforeachtable? 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,255)">FROM?# temp? ORDER? BY?TableName
DROP? temp
?
检查数据库里每个表或索引视图的数据、索引及text、ntext?和image?页的完整性
-- 下列语句需在单用户模式下执行（sp_dboption?'db_name',?'single?user',?'true'） ,将true改成false就又变成多用户了 exec?sp_msforeachtable?" dbcc?checktable( 4.参数@whereand的用法：

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

?@whereend,可以这么写

?AND?o.name?in?( Table1 Table2 '?

又如：

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

?sp_MSforeachtable? Update???Set?NOTE= '''' ?Where?NOTE?is?NULL '
? ? ? ? ? ? ? ? ?,Table3 ) '

5. "?"特别说明：

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

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

6.小结

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

?USE?MASTER

GO
proc?sp_MSforeachObject
? @objectType? int = 1,
? 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?( is? not? null)
?? exec( @precommand)
? ?Defined??@isobject?for?save?object?type? Declare? @isobject? varchar( 256)
? @isobject case? when? 1? then? IsUserTable '
????????? when? 2? IsView when? 3? IsTrigger when? 4? IsProcedure when? 5? IsDefault '??
????????? when? 6? IsForeignKey when? 7? IsScalarFunction when? 8? IsInlineFunction when? 9? IsPrimaryKey when? 10? IsExtendedProc '???
????????? when? 11? IsReplProc when? 12? 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 ] ]] )?+? ?+? . ?+
REPLACE(object_name(id),0)">?from?dbo.sysobjects?o? '
???????? +?N ?where?OBJECTPROPERTY(o.id,0)">''' + + ''' )?=?1? ' +N ?and?o.category?&? '? +? ?=?0? '
??????? @whereand)
? @retval? int
? @retval? =? @@error
? =? 0)
?? exec? =?sp_MSforeach_worker? @command1,? @replacechar,0)">@command2,0)">@command3
? =? 0? and? @postcommand)
? return? @retval
GO

我们来测试一下:

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

? 2011??EricHu

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

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

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

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

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

（编辑：李大同）

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