sp_MSforeachtable Stored Procedure in SQL Server 2008

In this article I am going to explain about sp_MSforeachtable system stored procedure.
  • 3577

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

  1. Loop through each table within a database.
  2. Display the size of all the tables in a database.
  3. Display total number of rows in all the table in a database.
  4. Disable all the constraints of all tables within a database.
  5. Delete all the data from tables in database.

Example

Display all table names under "person" database.

USE person

EXEC sp_MSforeachtable 'SELECT ''?'''

Output:

sp_MSforeachtable sql.jpg

© 2013 dotNetheaven. All rights reserved.