Scenario Proposal
There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works?
Solution
SQL Server has couple of undocumented system stored procedures, in the master database, which allow you to loop through all/selected databases using sp_MSforeachdb system stored procedure or loop through all/selected user tables using sp_MSforeachtable system stored procedure. You can even extend the functionality for views, stored procedures etc by using the sp_MSforeach_worker stored procedure, which is in fact used by the above two stored procedures as well.
These two system stored procedures use almost the similar set of parameters (more details in the below table) and return an integer value.
| 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, |
Script #1 contains a simple script to demonstrate the use of sp_MSForEachTable stored procedure. The first script lists all the tables and total number of rows in the current database whereas the second script displays the space used by each table in the current database.
| 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 usage of last script to use other parameters. This script creates a temporary table to hold the resultsets returned by the sp_spaceused stored procedure in the pre-execute phase. Then with @command1 it updates the statistics for all the tables and with @command2 it inserts the results to the temporary table created in the pre-execute phase. Further it narrows down the list of tables to consider, which is only tables belonging to HumanResources schema by using the @whereand parameter. Finally after execution of all these commands (during post execution) it selects records from the temporary table and then drops 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 |
By default sp_MSForEachTable internally uses OBJECTPROPERTY(o.id, N”IsUserTable”) = 1 to consider only user tables. You can change this default behavior by using @whereand parameter to consider system tables or views or stored procedures or combination of these etc. For example in Script #3, the first script uses the last script as above and considers both user tables as well as system tables. In the second script it considers only views and displays its text likewise in last script it considers only stored procedures and displays its text.
| 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’ GoUseAdventureWorks 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’ GoUseAdventureWorks 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 demonstrate the usage of sp_MSForEachDb stored procedure. The first script runs DBCC CHECKDB command against all the database to check the allocation, logical and physical structural integrity of all the objects inside a database. The second script first excludes the system databases from the consideration and takes a backup of all the 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 except system databasesDECLARE @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 |
Thanks for reading this article,
Next steps :
- Add this script to your database toolkit
- Share this with your colleagues because Sharing is Learning
- Comment below if you need any assistance