不用游标来循环访问SQLSERVER数据库对象
Parameters Type sp_Msforeachtable sp_Msforeachdb Description @precommand nvarchar(2000) Yes Yes This command is executed before any commands and can be used for setting up an environment for commands execution. @command1 nvarchar(2000) Yes
Parameters |
Type |
sp_Msforeachtable |
sp_Msforeachdb |
Description |
@precommand |
nvarchar(2000) |
Yes |
Yes |
This command is executed before any commands and can be used for setting up an environment for commands execution. |
@command1 |
nvarchar(2000) |
Yes |
Yes |
First command to be executed against each table/database. |
@command2 |
nvarchar(2000) |
Yes |
Yes |
Second command to be executed against each table/database. |
@command3 |
nvarchar(2000) |
Yes |
Yes |
Third command to be executed against each table/database. |
@postcommand |
nvarchar(2000) |
Yes |
Yes |
This command is executed after any other commands and can be used for cleanup process after commands execution. |
@replacechar |
nchar(1) |
Yes |
Yes |
Default value is “?” which represents the database/table name. You may need to change this value if you want “?” mark to be used in your query. |
@whereand |
nvarchar(2000) |
Yes |
No |
With this you can specify the filtering criteria for your table collection. For details see the script div, |
脚本1演示了sp_MSForEachTable的用法。第1条语句列出当前库所有的表和总的记录数,而语句2输出当前库下各表的空间占用情况。(注:在@cmd里用’’表示单引号,如select ‘’?’’)
Script #1 : sp_MSForEachTable system stored procedure |
–List all the tables of current database and total no rows in it –List all the tables of current database and space used by it EXECUTE sp_MSforeachtable ‘EXECUTE sp_spaceused [?];’; |
脚本2扩展了上一个脚本的参数使用。在@pre命令里它创建一个临时表来保存sp_spaceused返回的结果集,然后用@cmd1来更新表的统计,@cmd2用来插入临时表。除此以外,它还通过@whereand过滤条件来缩小范围,只针对HumanResources这个schema下的表。最后在@post命令中读取临时表并删除它。
Script #2 : sp_MSForEachTable system stored procedure |
–Creates a temporary table to hold the resultsets |
sp_MSForEachTable默认使用OBJECTPROPERTY(o.id, N”IsUserTable”) = 1作为where条件,即只针对用户表进行操作。你可以通过@whereand加入系统表、视图、存储过程或者所有这些以及其他对象。例如在以下的脚本3中,语句1在上面脚本基础上加入了系统表,即对象既包括用户表也包括系统表。在语句2中,分别只显示视图和存储过程的定义。
Script #3 : sp_MSForEachTable system stored procedure |
–Creates a temporary table to hold the resultsets Use AdventureWorks Use AdventureWorks |
脚本4演示了sp_MSForEachDb的用法。语句1对所有db运行dbcc checkdb,以检查所有对象的分配、逻辑和物理上的结构性完整度。语句2首先过滤系统数据库,再对所有用户数据库实施备份。
Script #4 : sp_MSForEachDb system stored procedure |
–Checks the allocation, logical and physical structural |
所有未公开的系统存储过程可能会在无通知的情况下变化,所以计划时需要考虑到这些。
上一篇: 二进制权限控制PHP类,JS控制函数
下一篇: c# 防盗链接