Two stored procedures not publicly available on SQL Server

Sp_MSForEachTable and sp_MSForEachDb are stored in the Master database of SQL Server, but they are not stored in the SQL Server online help, that is to say, Microsoft has not compiled these two stored procedures into the SQL Server books online. So what is the objective of Microsoft to design these two system stored procedures? What can these two system stored procedures do? In fact, Microsoft designed these two system stored procedures to support writing a single SQL statement, this statement can perform the same action on all databases on the current server or on all tables in the current database. The following describes the two stored procedures.

First, let's take a look at this SQL statement: exec sp_MSForEachDb @ Command1 = "print '? '", The SQL statement from all data names in the current server. Execution result

The @ Command1 parameter is used to specify the actions that a stored procedure will perform on each database. The question mark is used to replace the database name. The stored procedure can specify up to three commands (@ Command2 and @ Command3 ). In the background, this stored procedure opens a cursor for each record in the sysdatabases table, and then dynamically assembles a batch process that will be executed cyclically on each record. This statement displays the number of user tables in each database: exec sp_MSForEachDb @ Command1 = "select count (name) from ?. Dbo. sysobjects where xtype = 'U' ", execution result

The following command creates a Report on the usage of each database space: exec sp_MSForEachDb @ Command1 = "use? Exec sp_Spaceused ", execution result

More interestingly, you can run the sp_spaceused process on all the tables in the current database: exec sp_MSForEachtable @ Command1 = "sp_Spaceused '? '", You can also get some records on each table: exec sp_MSForEachtable @ Command1 =" print '? '", @ Command2 =" select count (*) from? ", The execution results are not arranged in the desired order. to sort the results by table name, you must use the @ whereand parameter: exec sp_MSForEachtable @ Command1 =" exec sp_Spaceused '? '", @ Whereand =" order by name ", this parameter is used to add a where subquery, but it is dynamically assembled during query, so you can add an Order by clause.

Here is another tips: If a command should be executed only once before or after a loop, you can use the @ precommand or @ postcommand parameter; you can also use the @ replacechar parameter to specify different placeholders for the database name and table name. This parameter is useful when the Command needs to use question marks, for example, wildcards in the Like clause.

In the past, when checking the capacity of all databases, looking at the capacity of all user tables in the specified database, and the number of records in all tables, you need to write some cursors to complete these tasks, which is not only time-consuming, and the efficiency of the cursor is not high. Well, with these two stored procedures, you can easily complete these tasks. If you are interested, you can check the source code of these two stored procedures, and we will not post them here.