不用游标来循环访问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

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,

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

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

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

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