【推荐】(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
''
,
''
Table2
''
,
''
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
''
]
''
, 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 
'
?
', 
'
dbo
'",
@objectType
=
2
EXEc sp_MSforeachObject 
@command1
="sp_changeobjectowner 
'
?
', 
'
dbo
'",
@objectType
=
3
EXEc sp_MSforeachObject 
@command1
="sp_changeobjectowner 
'
?
', 
'
dbo
'",
@objectType
=
4