Share via

SQL Server Undocumented Stored Procedure - sp_MSForEachTable

  • Article

This wiki page is about an undocumented stored procedure sp_MSForEachTable.

A more detailed list of undocumented stored procedures is at the following wiki -
http://social.technet.microsoft.com/wiki/contents/articles/16975.sql-server-undocumented-stored-procedures.aspx

It is strongly recommended to avoid using undocumented features of SQL Server in your Production environment.

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable
http://www.sqlservercurry.com/2009/04/8-common-uses-of-undocumented-stored.html

Display the size of all tables in a database
Display Number of Rows in all Tables in a database
Rebuild all indexes of all tables in a database
Disable all constraints of all tables in a database
Disable all Triggers of all tables in a database
Delete all data from all tables in your database
To RESEED all table to 0
Reclaim space from dropped variable-length columns in tables or indexed views
Update Statistics of all Tables in a database

Queries :
This script will give the full table name, the table name, and the schema:

Exec sp_MSForEachTable '
 Select ''?'' as [Full Name]
 , PARSENAME(''?'',1) as [Table]
 , PARSENAME(''?'',2) as [Schema]
 '