欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

不用游标来循环访问SQLSERVER数据库对象

程序员文章站 2022-05-09 15:42:35
...

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
EXEC sp_MSForEachTable ‘SELECT ”?” as TableName, COUNT(1)
as TotalRows FROM ? WITH(NOLOCK)’

–List all the tables of current database and space used by it EXECUTE sp_MSforeachtable ‘EXECUTE sp_spaceused [?];’;
GO

脚本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
–returned by sp_spaceused and before calling it,
–it updates the statistics for each table
–Filter out tables of HumanResources schema only
EXECUTE sp_MSforeachtable
@precommand = ‘CREATE TABLE ##Results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)’,
@command1 = ‘UPDATE STATISTICS ?;’,
@command2 = ‘INSERT INTO ##Results EXECUTE sp_spaceused [?];’,
@whereand = ‘and schema_name(schema_id) = ”HumanResources”’,
@postcommand = ‘SELECT * FROM ##Results; DROP TABLE ##Results’
Go

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
–returned by sp_spaceused and before calling it,
–it updates the statistics for each table
–Note it consider both user and system tables
EXECUTE sp_MSforeachtable
@precommand = ‘CREATE TABLE ##Results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)’,
@command1 = ‘UPDATE STATISTICS ?;’,
@command2 = ‘INSERT INTO ##Results EXECUTE sp_spaceused [?];’,
@whereand = ‘or OBJECTPROPERTY(o.id, N”IsSystemTable”) = 1′,
@postcommand = ‘SELECT * FROM ##Results; DROP TABLE ##Results’
Go

Use AdventureWorks
GO
–Display the views’ script text
EXECUTE sp_MSforeachtable
@command1 = ‘sp_helptext [?];’,
@whereand = ‘and OBJECTPROPERTY(o.id, N”IsUserTable”) = 0
or OBJECTPROPERTY(o.id, N”IsView”) = 1′
Go

Use AdventureWorks
GO
–Display the stored procedures’ script text
EXECUTE sp_MSforeachtable
@command1 = ‘sp_helptext [?];’,
@whereand = ‘and OBJECTPROPERTY(o.id, N”IsUserTable”) = 0
or OBJECTPROPERTY(o.id, N”IsProcedure”) = 1′
Go

脚本4演示了sp_MSForEachDb的用法。语句1对所有db运行dbcc checkdb,以检查所有对象的分配、逻辑和物理上的结构性完整度。语句2首先过滤系统数据库,再对所有用户数据库实施备份。

Script #4 : sp_MSForEachDb system stored procedure

–Checks the allocation, logical and physical structural
–integrity of all the objects of all the databases
EXEC sp_MSForEachdb
@command1 = ‘DBCC CHECKDB([?])’
GO –Does Backup of all the databases except system databases
DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = ‘IF ”?” NOT IN(”master”, ”model”, ”tempdb”, ”msdb”)’ + ‘BEGIN ‘
+ ‘Print ”Backing up ? database…”;’
+ ‘BACKUP DATABASE [?] TO DISK=”’ + ‘D:\?_’ + replace(convert(varchar,GETDATE(),120),’:',”) + ‘.bak”’
+ ‘END’
EXEC sp_MSForEachdb
@command1 = @cmd1
GO

所有未公开的系统存储过程可能会在无通知的情况下变化,所以计划时需要考虑到这些。