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, Vista, etc.




download SQL Server 2014



MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes


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 ?'

You should realize that the above select will return the table names in the 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 ?'

The 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)
  )


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






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









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