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

最新推荐文章于 2022-03-29 17:51:50 发布

怀柔远人

最新推荐文章于 2022-03-29 17:51:50 发布

阅读量422

点赞数

文章标签： mysql 不用游标循环

本文链接：https://blog.csdn.net/weixin_34541572/article/details/113327457

版权

本文介绍了如何在SQLSERVER中利用sp_MSforeachtable和sp_MSforeachdb系统存储过程，无须游标就能循环访问数据库对象。通过示例脚本展示了更新统计信息、获取空间使用情况、筛选特定模式下的表以及备份数据库等功能，同时提醒注意系统存储过程的潜在变更风险。

摘要由CSDN通过智能技术生成

Parameters

Type

sp_Msforeachtable

sp_Msforeachdb

Description

@precommand

nvarchar(2000)

Yes

This command is executed before any commands and can be used for setting up an environment for commands execution.

@command1

nvarchar(2000)

Yes

First command to be executed against each table/database.

@command2

nvarchar(2000)

Yes

Second command to be executed against each table/database.

@command3

nvarchar(2000)

Yes

Third command to be executed against each table/database.

@postcommand

nvarchar(2000)

Yes

This command is executed after any other commands and can be used for cleanup process after commands execution.

@replacechar

nchar(1)

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

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 [?];’;

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

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’

Use AdventureWorks

–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′

Use AdventureWorks

–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′

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

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

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)