Cyclically accessing SQLSERVER database objects without using cursors

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.