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

http://blog.rds.aliyun.com/2014/06/26/不用游标来循环访问sqlserver数据库对象/

问题：

你经常需要遍历所有db或对象来实施某些任务。例如你要对所有db使用dbcc（注：一种sqlserver系统管理的内部命令），或对服务器上所有库进行备份，或对它们的所有表重建索引，在或者想要得知某库下每个表的大小。最简单的方法是建立一个游标来遍历它，这需要你写很多行代码，对这类任务有没有什么简化代码的方法呢？

解决方案：

在master库（注：sqlserver系统管理库，类似于mysql的mysql库）中有许多未公开的系统存储过程，你可以通过使用sp_MSforeachdb去遍历所有/选择性的库，sp_MSforeachtable去遍历所有/选择性的表。你甚至可以扩展其来操作视图、存储过程等对象，即使用sp_MSforeach_worker，它实际上是上述2种的合集。

这2个系统存储过程使用几乎相同的参数，并且返回整形。

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 section,

脚本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

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

https://raresql.com/2014/02/11/sql-server-how-to-filter-databases-in-sp_msforeachdb/

sp_MSforeachdb过滤某些数据库

EXEC sp_MSforeachdb
@command1='IF ''?''
IN (''AdventureWorks2012'',''AdventureWorks2012_test'')
BEGIN
       SELECT name,object_id,modify_date
       FROM ?.sys.tables WHERE [name]=''Person''
END'

实例下各个数据库数据分析2

EXEC sys.[sp_MSforeachdb] @command1 = N'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'',''ReportServer'',''ReportServerTempDB'',''distribution'')
BEGIN
     USE ?
IF OBJECT_ID(''tempdb..#TablesSizes'') IS NOT NULL
    DROP TABLE #TablesSizes

CREATE TABLE #TablesSizes
    (
      TableName sysname ,
      Rows BIGINT ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    )

DECLARE @sql NVARCHAR(MAX)
SELECT  @sql = COALESCE(@sql, '''') + ''INSERT INTO #TablesSizes execute sp_spaceused '''''' + QUOTENAME(TABLE_SCHEMA,''[]'') + ''.''+ QUOTENAME(Table_Name, ''[]'') +''''''''
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = ''BASE TABLE''
EXECUTE (@SQL)
alter table #TablesSizes add [DBNAME] VARCHAR(100) null
update #TablesSizes set [DBNAME] = ''?''
SELECT  *
FROM    #TablesSizes
ORDER BY Rows DESC
       
END'    -- nvarchar(2000)

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

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

公告