`

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

 
阅读更多

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


sp_Msforeachtable与sp_Msforeachdb详解


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

一.简要介绍:

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

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

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

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

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

EXECsp_MSforeachtable"EXECUTEsp_spaceused'?'"

二.各参数说明:

@command1nvarchar(2000),--第一条运行的SQL指令
@replacecharnchar(1)=N'?',--指定的占位符号
@command2nvarchar(2000)=null,--第二条运行的SQL指令
@command3nvarchar(2000)=null,--第三条运行的SQL指令
@whereandnvarchar(2000)=null,--可选条件来选择表
@precommandnvarchar(2000)=null,--执行指令前的操作(类似控件的触发前的操作)
@postcommandnvarchar(2000)=null--执行指令后的操作(类似控件的触发后的操作)

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

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

usemaster
execsp_helptextsp_MSforeachtable
execsp_helptextsp_Msforeachdb

三、使用举例:

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

execsp_MSforeachtable@command1="sp_spaceused'?'"

--获得每个表的记录数和容量:
EXECsp_MSforeachtable@command1="print'?'",
@command2="sp_spaceused'?'",
@command3="SELECTcount(*)FROM?"

--获得所有的数据库的存储空间:
EXECsp_MSforeachdb@command1="print'?'",
@command2="sp_spaceused"

--检查所有的数据库
EXECsp_MSforeachdb@command1="print'?'",
@command2="DBCCCHECKDB(?)"

--更新PUBS数据库中已t开头的所有表的统计:
EXECsp_MSforeachtable@whereand="andnamelike't%'",
@replacechar='*',
@precommand="print'UpdatingStatistics.....'print''",
@command1="print'*'updatestatistics*",
@postcommand="print''print'CompleteUpdateStatistics!'"

--删除当前数据库所有表中的数据
sp_MSforeachtable@command1='Deletefrom?'
sp_MSforeachtable@command1="TRUNCATETABLE?"

--查询数据库所有表的记录总数
CREATETABLE#temp(TableNameVARCHAR(255),RowCntINT)
EXECsp_MSforeachtable'INSERTINTO#tempSELECT''?'',COUNT(*)FROM?'
SELECTTableName,RowCntFROM#tempORDERBYTableName
DROPTABLE#temp

--检查数据库里每个表或索引视图的数据、索引及text、ntext和image页的完整性
--下列语句需在单用户模式下执行（sp_dboption'db_name','singleuser','true'）
--,将true改成false就又变成多用户了
execsp_msforeachtable"dbccchecktable('?',repair_rebuild)"

4.参数@whereand的用法：

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

@whereend,可以这么写

@whereand='ANDo.namein(''Table1'',''Table2'',.......)'

又如：

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

sp_MSforeachtable@command1='Update?SetNOTE=''''WhereNOTEisNULL'
,@whereand='ANDo.namein(''Table1'',''Table2'',''Table3'')'

5. "?"特别说明：

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

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

6.小结

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

USEMASTER

GO
CREATEprocsp_MSforeachObject
@objectTypeint=1,
@command1nvarchar(2000),
@replacecharnchar(1)=N'?',
@command2nvarchar(2000)=null,
@command3nvarchar(2000)=null,
@whereandnvarchar(2000)=null,
@precommandnvarchar(2000)=null,
@postcommandnvarchar(2000)=null
as
/*Thisprocreturnsoneormorerowsforeachtable(optionally,matching@where),witheachtabledefaultingtoits
ownresultset*/
/*@precommandand@postcommandmaybeusedtoforceasingleresultsetviaatemptable.*/
/*Preprocessorwon'treplacewithinquotessohavetousestr().*/
declare@mscatnvarchar(12)
select@mscat=ltrim(str(convert(int,0x0002)))
if(@precommandisnotnull)
exec(@precommand)
/*Defined@isobjectforsaveobjecttype*/
Declare@isobjectvarchar(256)
select@isobject=case@objectTypewhen1then'IsUserTable'
when2then'IsView'
when3then'IsTrigger'
when4then'IsProcedure'
when5then'IsDefault'
when6then'IsForeignKey'
when7then'IsScalarFunction'
when8then'IsInlineFunction'
when9then'IsPrimaryKey'
when10then'IsExtendedProc'
when11then'IsReplProc'
when12then'IsRule'
end
/*Createtheselect*/
/*Use@isobjectvariableissteadofIsUserTablestring*/
EXEC(N'declarehCForEachcursorglobalforselect''[''+REPLACE(user_name(uid),N'']'',N'']]'')+'']''+''.''+''[''+
REPLACE(object_name(id),N'']'',N'']]'')+'']''fromdbo.sysobjectso'
+N'whereOBJECTPROPERTY(o.id,N'''+@isobject+''')=1'+N'ando.category&'+@mscat+N'=0'
+@whereand)
declare@retvalint
select@retval=@@error
if(@retval=0)
exec@retval=sp_MSforeach_worker@command1,@replacechar,@command2,@command3
if(@retval=0and@postcommandisnotnull)
exec(@postcommand)
return@retval
GO

我们来测试一下:

--获得所有的存储过程的脚本:
EXEcsp_MSforeachObject@command1="sp_helptext'?'",@objectType=4

--获得所有的视图的脚本:
EXEcsp_MSforeachObject@command1="sp_helptext'?'",@objectType=2

--比如在开发过程中,没一个用户都是自己的OBJECTOWNER,所以在真实的数据库时都要改为DBO:
EXEcsp_MSforeachObject@command1="sp_changeobjectowner'?','dbo'",@objectType=1
EXEcsp_MSforeachObject@command1="sp_changeobjectowner'?','dbo'",@objectType=2
EXEcsp_MSforeachObject@command1="sp_changeobjectowner'?','dbo'",@objectType=3
EXEcsp_MSforeachObject@command1="sp_changeobjectowner'?','dbo'",@objectType=4

© 2011EricHu

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

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

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

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

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

分享到：
| [推荐] (SqlServer)批量清理指定数据库中 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    系统存储过程sp_MSforeachtable和sp_MSforeachdb使用说明

    系统存储过程`sp_MSforeachtable`和`sp_MSforeachdb`是SQL Server中非常实用的工具，主要用于批量处理数据库中的表或所有数据库。这两个存储过程是Microsoft SQL Server自6.5版本以来内置的非公开存储过程，它们位于...

    浅析SQL SERVER一个没有公开的存储过程

    SQL Server中的`sp_MSforeachtable`是一个非常实用但未公开的系统存储过程，它允许DBA（数据库管理员）执行对所有用户表或特定表的批量操作，极大地简化了数据库维护和管理的工作。这个存储过程从SQL Server 6.5版本...

    SqlServer删除所有表数据语句

    在上面的代码中，我们使用了 `sp_MSForEachTable` 系统存储过程，该过程可以对所有表执行指定的操作。在这里，我们首先禁用所有表的约束和触发器，然后删除所有表的数据，最后重新启用约束和触发器。 删除所有表 ...

    Sql Server中清空所有数据表中的记录

    其实删除数据库中数据的方法并不复杂，为什么我还要多此一举呢，一是我这里介绍的是删除数据库的所有数据，因为数据之间可能形成相互约束关系，删除操作可能陷入死循环，二是这里使用了微软未正式公开的sp_...

    SQL2005遍历系统所有表及库的存储过程

    `sp_MSforeachtable`是SQL Server提供的一种非官方系统存储过程，它可以从SQL Server 6.5版本开始使用，主要用于在当前数据库的所有用户表上执行特定的命令。 #### 参数说明 `sp_MSforeachtable`接受多个参数： -...

    如何对SQL数据表和数据库进行迭代操作

    本文主要讨论了两个不常出现在SQL Server在线教科书中的系统存储过程：`sp_MSForEachDB`和`sp_MSForEachTable`，它们能帮助我们更加便捷地处理数据库的管理和维护任务。 `sp_MSForEachDB`是用于迭代服务器上所有...

    SQL Server将数据导出SQL脚本的方法

    通常，SQL Server Management Studio (SSMS) 提供的功能仅能导出数据库架构，包括表结构、索引、视图、存储过程等，但不直接包含数据。要导出数据表的内容，确实需要手动编写SQL语句或者使用特定的存储过程。本文将...

    SQLSERVER2000一些内置存储过程用法和说明

    ### SQL Server 2000 内置存储过程用法与说明 #### 一、引言 随着互联网技术的发展和数据库应用的普及，SQL Server 2000 作为一款主流的关系型数据库管理系统（RDBMS），其安全性问题日益受到关注。在实际应用中，...

    SQL Server数据表和数据库

    本文介绍了master数据库中两个非常有用的存储过程：sp_MSForEachDB和sp_MSForEachTable。

    Sql Server强制清空所有数据表中的记录.docx

    请注意，这种方法可能会有潜在风险，因为它涉及全局系统存储过程`sp_MSForEachTable`，这不是一个官方支持的SQL Server组件，尽管在许多情况下它是有效的。另外，禁用所有触发器可能导致数据一致性问题，如果这些...

    用SQL语句完成SQL Server数据库的修复

    这里`sp_msforeachtable`是一个系统存储过程，它可以遍历数据库中的所有表，并执行指定的命令。`@command1`参数指定了要执行的命令。 ##### 4. 恢复多用户模式 修复完成后，需要将数据库恢复为多用户模式： ```...

    批量更改数据库表的所有者

    `sp_MSforeachtable`是一个非文档化的存储过程，主要用于遍历数据库中的所有表，并对每张表执行相同的SQL语句。它可以极大地简化批处理任务。使用格式如下： ```sql EXEC sp_MSforeachtable @command1 = N'your SQL...

    SqlServer常用SQL

    除此之外，还有其他重要概念，如JOIN操作、索引管理、事务处理、视图和存储过程的创建与调用等，都是SQL Server数据库管理员和开发人员需要掌握的核心技能。在实际工作中，不断学习和实践这些知识，将使你在SQL ...

    Sql Server强制清空所有数据表中的记录.pdf

    这里提供了一个名为`PROC_DeleteAllData`的存储过程，它能够帮助我们安全地清空SQL Server数据库中的所有数据。 首先，我们来看存储过程的各个部分： 1. **关闭约束**： 使用`sp_MSForEachTable`系统存储过程遍历...

    sqlserver 常用存储过程集锦

    本篇将详细讲解文中提到的几个常用存储过程，以及一些与之相关的SQL Server存储过程知识。 1. 分页查找数据：存储过程`[dbo].[GetRecordSet]` 这个存储过程用于实现分页查询，它是数据库应用中常见的需求。通过...

    使用SQL语句清空数据库所有表的数据.docx

    然而，由于`sp_msforeachtable`是非标准的扩展存储过程，微软并不建议在生产环境中广泛使用，因为它可能在未来的SQL Server版本中被弃用。 总结来说，清空数据库所有表数据的SQL方法主要有三种：直接拼接字符串执行...

    SQLServer修改表所有者.pdf

    在批量修改多个表的所有者时，可以使用`sp_MSforeachtable`这个实用的系统存储过程。例如，如果你想将当前数据库中所有的表的所有者更改为`dbo`，可以这样操作： ```sql EXEC sp_MSforeachtable 'exec sp_...

Magicbox
Global site tag (gtag.js) - Google Analytics