sp_MSforeachtable Stored Procedure in SQL Server 2008
In this article I am going to explain about sp_MSforeachtable system stored procedure.
Introduction
In this article I am going to explain about sp_MSforeachtable system stored procedure. Several times in our project we needed to process through all the tables within a database. It is an undocumented stored procedure which is not listed in MSDN books.
sp_MSforeachtable Stored Procedure
This stored procedure allows you to do a specific task or run a query against all the tables in a database. This stored procedure is stored in "master" database. For example: If you want to update statistics for all table in the database, count number of tables, display all the tables stored in particular database or delete all the tables from a database. It becomes very tedious to perform delete , update or any other operation on each table one by one, so this is the best way to run a query against each table in a database.
Syntax
USE Database_Name
EXEC sp_MSforeachtable @command
|
where,
Database_Name specifies the name of database in which you want to run query against each table under that database.
@command specifies command that has to run against each table.
Use of sp_MSforeachtable Stored Procedure
- Loop through each table within a database.
- Display the size of all the tables in a database.
- Display total number of rows in all the table in a database.
- Disable all the constraints of all tables within a database.
- Delete all the data from tables in database.
Example
Display all table names under "person" database.
USE person
EXEC sp_MSforeachtable 'SELECT ''?'''
Output: