SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2016
download SQL Server 2014

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 :

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.


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)

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 '
  SUBSTRING(''?'', 8, Len(''?'')-8) AS TableName,
  syscolumns.name ColumnName,
  CASE systypes.name
    WHEN ''sysname'' THEN ''nvarchar''
    ELSE systypes.name
  END AS Type,
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
  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].

  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,
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
  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 ?'

row count for all database tables using sp_MSForEachTable on SQL Server

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 '
 select * from sys.columns
  name = ''name'' and
  object_id = object_id(''?'')

UPDATE STATISTICS For Every Table in the Database using sp_MSForEachTable

The following sql command will update statistics for each table in a database.


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

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2016 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems