How to iterate SQL Server data tables and databases

This article introduces two stored procedures that are very useful in the master database but not mentioned in SQL Server online textbooks.

These system processes are very convenient for processing the following tasks, such as determining the storage space used, the number of rows, and the user table index. In the first process, sp_MSForEachDB executes three commands for each database on the server of interest.

◆ @ Command1: The first command to be executed
◆ @ Replacechar: Replace "?" With another placeholder
◆ @ Command2: The second command to be executed
◆ @ Command3: The third command to be executed
◆ @ Precommand: The Command executed before entering the loop
◆ @ Postcommand: The Command executed after the loop ends.

Each Command set, even if the set contains only one command) is executed on each database as a batch. Therefore, when we want to output the captured results to the text instead of the standard result set table, this will be very useful.

To achieve this, select the query button in the menu | output result | output to text or press the shortcut key [Ctrl] T.

The following code returns the number of user data tables in each database on the server:

exec sp_MSForEachDB @command1 = "use ? exec sp_SpaceUsed"

The abbreviated output looks like this: The brief output may be as follows:

Database Name database size unallocated space size

---------------------------------------
Master 5.25 MB1.26 MB

Reserved data index_size unused
------------------------------------
2808 KB 1144 KB 1080 KB 584 KB

In the second process, sp_MSForEachTable accepts 7 parameters:

◆ @ Command1: The first command to be executed
◆ @ Replacechar: Replace "?" With another placeholder
◆ @ Command2: The second command to be executed
◆ @ Command3: The third command to be executed
◆ @ Whereand: Where Condition Statement (or Order By Statement)
◆ @ Precommand: The Command executed before entering the loop
◆ @ Postcommand: The Command executed after the loop ends.

By naming the parameters to be passed, you can skip the requirements for passing null values. The @ replacechar parameter is very useful when the command to be executed contains a question mark. The implementation of the @ whereand parameter can narrow the output range according to the filter.

You can also add an order by statement. In the following example, the number of rows in each data table in the AdventureWorks database is returned and sorted by data indication:

exec sp_MSForEachTable @command1 = "Print '?'" , @command2 = "select count(*) from ?" , @whereand = "ORDER BY 1"

The following are some output results:

[HumanResources].[Department] ----------- 16 [HumanResources].[Employee] ----------- 290 [HumanResources].[EmployeeAddress] ----------- 290 [HumanResources].[EmployeeDepartmentHistory] ----------- 296

I like to sort data tables by mode and table name.

Related Articles]

• Use C # To create a stored procedure for SQL Server

• SQL Server automatically analyzes access history logs and provides optimization policies

• How does SQL Server access sybase database tables?