SQL Server Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable in Example T-SQL Codes
SQL Server tutorial shows how to use sp_MSForEachDB and sp_MSForEachTable undocumented stored procedures for database programmers and SQL Server administrators to ease their daily tasks.
The literal ? is used as a token which is replace with database name or table name according to the executed stored procedure "sp_MSForEachDB" or "sp_MSForEachTable".
If you want to select the database name or the table name as a literal in the t-sql expression you should use double single quotes around the ? literal.
Also the sp_MSForEachDB syntax enables the SQL Server developers or administrators to use [?] instead of ?.
Using token ? in the format "[?]" will rescue in case the database names in the Microsoft SQL Server instance have space characters.
But the same point is just the opposite for the undocumented sp_MSForEachTable proc syntax.
For example, if a database name is "Test Database" then executing the below t-sql command will cause the following error :
EXEC sp_MSForEachDB 'Use ?; SELECT DB_NAME()'
/*
Could not locate entry in sysdatabases for database 'Test'. No entry found with that name. Make sure that the name is entered correctly.
*/
So we can say that the correct syntax for the sp_MSForEachDB and sp_MSForEachTable un-documented procedures is using [?] instead of ? which is pointing to databases in the MSSQL Server
EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'
You can get an idea on how the sp_MSForEachTable syntax works with ? which is representing the table name in the format schema-name.table-name.
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?' -- SUCCESSFULL
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM [?]' -- FAIL
T-SQL Sample Queries using sp_MSForEachDB
The below t-sql example codes will count database objects and user procedures for each database and will list these count values with the database name beside for the MS SQL Server instance.
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName, COUNT(*) AS ObjectCount FROM [?].sys.objects'
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName, COUNT(*) AS ObjectCount FROM [?].sys.procedures'
The following sql code sp_MSForEachDB example will list system files for each database in the current MS SQL Server instance.
EXEC sp_MSForEachDB 'SELECT ''?'', * FROM [?]..sysfiles'
And similar to the above sql example listing database files detail, the following t-sql code will run the sp_helpfile for every SQL Server database in the installed SQL Server instance.
EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile'
You can use the "USE" command in order to change the database scope of the query.
This will execute the following query on the related database which is changing everytime with the sp_MSForEachDB.
EXEC sp_MSForEachDB 'USE [?]; SELECT ''?'' AS DatabaseName, COUNT(*) AS ProcedureCount FROM sys.procedures'
Of course, you can remove the EXEC command and make call to the sp_MSForEachDB or sp_MSForEachTable MS SQL Server stored procedures directly.
SHRINKDATABASE For Every Database in the SQL Server Instance using sp_MSForEachDB
The following t-sql sp_MSForEachDB command will shrink every database in the related SQL Server instance.
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'
T-SQL Sample Queries using sp_MSForEachTable
The following t-sql query is a statement which displays rows count for each table in a database.
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
SQL programmers can realize that above Select statement will return table names in format [schema name].[table name]
To remove the brackets [ and ] , you can execute the following altered t-sql query.
EXEC sp_MSForEachTable 'SELECT SUBSTRING(''?'', 8, Len(''?'')-8), COUNT(*) FROM ?'
Below T-SQL sp_MSForEachTable example will execute the sp_SpaceUsed for everytable in the current MS SQL Server database and will store the results or the outcome of the sp_SpaceUsed system stored procedure in the spSpaceUsed table.
CREATE TABLE spSpaceUsed (
TableName sysname,
Rows int,
Reserved varchar(255),
Data varchar(255),
Index_Size varchar(255),
Unused varchar(255)
)
INSERT INTO spSpaceUsed
EXEC sp_MSForEachTable 'EXEC sp_SpaceUsed ''?'''
SELECT * FROM spSpaceUsed
This t-sql query will diplay column names with type and size for every table in a database.
EXEC sp_MSForEachTable '
SELECT
SUBSTRING(''?'', 8, Len(''?'')-8) AS TableName,
syscolumns.name ColumnName,
CASE systypes.name
WHEN ''sysname'' THEN ''nvarchar''
ELSE systypes.name
END AS Type,
syscolumns.length,
syscolumns.prec
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
WHERE
syscolumns.id = (
SELECT id FROM sysobjects (NoLock)
WHERE name = SUBSTRING(''?'', 8, Len(''?'')-8)
)
'
Actually the above t-sql query command will execute just as shown below for let's say the table name is [dbo].[Logs].
SELECT
SUBSTRING('[dbo].[Logs]', 8, Len('[dbo].[Logs]')-8) AS TableName,
syscolumns.name ColumnName,
CASE systypes.name
WHEN 'sysname' THEN 'nvarchar'
ELSE systypes.name
END AS Type,
syscolumns.length,
syscolumns.prec
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
WHERE
syscolumns.id = (
SELECT id FROM sysobjects (NoLock)
WHERE name = SUBSTRING('[dbo].[Logs]', 8, Len('[dbo].[Logs]')-8)
)
Another SQL example with sp_MSForEachTable stored procedure is as following. In this sp_MSForEachTable sample script, I will show how SQL programmers can use this undocumented yet powerful sp_MSForEachTable stored procedure to list row counts of database tables instead of using a SQL cursor loop structure.
use [kodyaz.development]
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
Further more SQL programmers can modify the above script by adding criterias like IF EXISTS() , etc.
For example, in the following sample SQL script sp_MSForEachTable stored procedure is used with IF EXISTS() statement.
Within the IF EXISTS(), sys.columns system view is queried if a table column named "NAME" exists or not.
If there is a column as "Name" then the number of all rows in that column is returned, otherwise nothing displayed on Query Editor for that database table.
use [kodyaz.development]
EXEC sp_MSForEachTable '
IF EXISTS(
select * from sys.columns
where
name = ''name'' and
object_id = object_id(''?'')
)
SELECT ''?'', COUNT(*) FROM ?
'
UPDATE STATISTICS For Every Table in the Database using sp_MSForEachTable
The following sql command will update statistics for each table in a database.
EXEC sp_MSForEachTable 'UPDATE STATISTICS ?'
More Tutorials on T-SQL sp_MSForEachTable Examples
sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications
Create Database Snapshot for all Databases on SQL Server