ParametersTypesp_Msforeachtablesp_MsforeachdbDescription @ precommandnvarchar (2000) YesYesThiscommandisexecutedbeforeanycommandsandcanbeusedforsettingupanenvironmentforcommandsexecution. @ command1nvarchar (2000) Yes
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
|
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 be 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 p, |
Script 1 demonstrates the usage of sp_MSForEachTable. Statement 2 lists all the tables and total number of records in the current database. Statement 2 lists the space usage of each table in the current database. (Note: Use ''in @ cmd to represent single quotes, for example, 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 |
Script 2 extends the parameter usage of the previous script. Run the @ pre command to create a temporary table to save the result set returned by sp_spaceused, and then use @ cmd1 to update table statistics. @ cmd2 is used to insert a temporary table. In addition, it also uses the @ whereand filter condition to narrow the scope, only for tables in the HumanResources schema. Finally, read the temporary table in the @ post command and delete it.
|
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 uses OBJECTPROPERTY (o. id, N "IsUserTable") = 1 as the where condition by default, that is, operations are performed only on user tables. You can add system tables, views, stored procedures, or all of these and other objects through @ whereand. For example, in script 3 below, Statement 1 adds a system table to the script above, that is, the object includes both the user table and the system table. In statement 2, only view and stored procedure definitions are displayed.
|
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 demonstrates the usage of sp_MSForEachDb. Statement 1 runs dbcc checkdb on all databases to check the allocation, logical, and physical structural integrity of all objects. Statement 2 first filters out the system database and backs up all user databases.
|
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 into T 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 |
All undisclosed system stored procedures may change without notice, so these need to be taken into account during planning.