sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online.
Use:
sp_MSforeachtable can be used to run a query against each table in the database.
Examples:
Updating statestics for each table in the database
Logging the space used by each table in the database
finding the number of rows for each table in database
Syntax:
[sql]
EXEC sp_MSforeachtable @command1,
@replacechar,
@command2,
@command3,
@whereand,
@precommand,
@postcommand
[/sql]
@command1 – Is the command that hs to be run against each table.
@replacechar – Is the replace charecter for table name. Default value is ?
@command2 – This is the second command that is run after @command1.
@command3 – This is the third command that is run after @command1 and @command2.
@whereand – This is the where clasue that can be used to filter tables.
@precommand – This command is run first by the procedure.It is run before processing any table
@postcommand – This command is run last by the procedure.It is run after processing all the tables.
Note: ? Can be used for table name in @command1,@command2 and @command3.
Example TSQL Queries:
Query1:
Query to list all the tables in the database
[sql]
EXEC sp_MSforeachtable ‘SELECT ”?”’
[/sql]
? – Represents table name
”?” is used as the name of the table is a string and has to be opened and closed in quotes in select clause.
Query2:
Query to save and list all the tables in the database:
[sql]
CREATE TABLE #Tables (TableName VARCHAR(255))
EXEC sp_MSforeachtable ‘INSERT INTO #Tables SELECT ”?”’
SELECT * FROM #Tables
[/sql]
Table names are saved into the temporary table #Tables.
Note: There will be a chage in resultsets for query1 and query2.
You can observe that you get multiple resultsets one for each table with query1 and a single resultset containing all table names for query2.
Query3:
Query to update statistics of all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘UPDATE STATISTICS ?’
[/sql]
The above query executes below commands if you are using Adwentureworks.
[sql]
UPDATE STATISTICS [Sales].[Store]
UPDATE STATISTICS [Production].[ProductPhoto]
…. etc
[/sql]
Query4:
Query to calculate the space used for each table.
[sql]
CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255),
unused VARCHAR(255) );
EXEC sp_MSforeachtable ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’
SELECT * FROM #Tables
SELECT sum(rows) from #Tables
[/sql]
The first resultset would be the detailed space used data for each table.
The second resultset will give the total number of rows in all the tables in the database.
Query5:
Using where clause with sp_MSforeachtable(@whereand clause).
Query to execute sp_spaceused procedure and fetch the resulset of all the tables in the database that begin with p.
[sql]
–drop table #Tables
CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255),
unused VARCHAR(255) );
EXEC sp_MSforeachtable
@command1 = ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’,
@whereand = ‘ and object_name(object_id) like ”p%” ‘
SELECT * FROM #Tables
[/sql]
Query6:
Query to delete data in all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘delete ?’
[/sql]
Query7:
Query to disable triggers on all tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started disabling triggers on table ?”,1,1)
ALTER TABLE ? DISABLE TRIGGER ALL ‘
[/sql]
Query8:
Query to truncate all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’
[/sql]
Query9:
Query to disable constaints on all tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started disabling constraints on table ?”,1,1)
ALTER TABLE ? NOCHECK CONSTRAINT ALL’
[/sql]
Query10:
Query to enable change tracking on all the tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started enabling change tracking on table ?”,1,1)
ALTER TABLE ? ENABLE CHANGE_TRACKING;’
[/sql]
Query11:
Query to rebuild clustered index/all non clusted indexes on all the tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started rebuild index on table ?”,1,1)
ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE);’
[/sql]
Query12:
Query to drop all the tables in the database
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started dropping table ?”,1,1)
DROP TABLE ?’
[/sql]