If we want to perform an action on each and every tables (as it is self explanatory “for each Table” ) in a database.
Common uses of this store procedure.
- Truncate all the tables
- Disable check constraints for all tables
- Run delete command on all the tables
- To get the size of all the tables in the database
- To rebuild all indexes of all the tables in the database
- Disable all constraints and triggers of all the tables in the database
- Delete all the data from all the tables in the database
- To RESEED all tables to 0
- To get the Number of Rows in all tables in a database
- Update the statistics of all the tables in a database
- Reclaim space from dropped variable-length columns in tables or indexed views of the database
MS Sql Server provides you with some undocumented stored procedure “sys.sp_MSforeachTable”. It allows you to process some code against each and every table in a single database. This is a system stored procedure which comes with SQL server by default. This function has ability to run single command or multiple commands against tables in a single database.
Syntax
sp_MSforeachtable [ @command1 = ] ‘command1’ [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]
Parameter
| Parameter |
Description |
| @command1 |
It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000). |
| @replacechar |
It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a “?”. |
| @command2 |
@command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute. |
| @command3 |
| @whereand |
This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000). |
| @precommand |
This command is to be run before processing any table. Its data type is nvarchar(2000). |
| @postcommand |
This command is to be run after the processing of all the tables. Its data type is nvarchar(2000). |
sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:
— Display the size of all tables in a database
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”
— Display Number of Rows in all Tables in a database
EXEC sp_MSforeachtable ‘SELECT ”?”, Count(*) as NumberOfRows FROM ?’
— Diable trigger on all the tables
EXEC sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
— Disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
— Enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
— To RESEED all table to 0, use this script
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)
‘
— Update Statistics of all Tables in a database
EXEC sp_MSforeachtable ‘UPDATE statistics ? WITH ALL’
