sp_MSforeachtable Examples – Uses – InDetail – msdn

Posted on : 21-12-2011 | By : Devi Prasad | In : SQL Server Basics, SQLServerPedia Syndication

Share:

2


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:

EXEC sp_MSforeachtable @command1,
@replacechar,
@command2,
@command3,
@whereand,
@precommand,
@postcommand

@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

EXEC sp_MSforeachtable 'SELECT ''?'''

? – 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:

CREATE TABLE #Tables  (TableName VARCHAR(255))
EXEC sp_MSforeachtable 'INSERT INTO #Tables SELECT ''?'''
SELECT * FROM #Tables

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.

EXEC sp_MSforeachtable 'UPDATE STATISTICS ?'

The above query executes below commands if you are using Adwentureworks.

 
UPDATE STATISTICS [Sales].[Store]
UPDATE STATISTICS [Production].[ProductPhoto]
.... etc

Query4:
Query to calculate the space used for each table.

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

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.

--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

Query6:
Query to delete data in all the tables in the database.

EXEC sp_MSforeachtable 'delete ?'

Query7:
Query to disable triggers on all tables

EXEC sp_MSforeachtable '
RAISERROR(''Started disabling triggers on table ?'',1,1)
ALTER TABLE ? DISABLE TRIGGER ALL '

Query8:
Query to truncate all the tables in the database.

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Query9:
Query to disable constaints on all tables

EXEC sp_MSforeachtable '
RAISERROR(''Started disabling constraints on table ?'',1,1)
ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Query10:
Query to enable change tracking on all the tables

EXEC sp_MSforeachtable '
RAISERROR(''Started enabling change tracking on table ?'',1,1)
ALTER TABLE ? ENABLE CHANGE_TRACKING;'

Query11:
Query to rebuild clustered index/all non clusted indexes on all the tables

EXEC sp_MSforeachtable '
RAISERROR(''Started rebuild index on table ?'',1,1)
ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE);'

Query12:
Query to drop all the tables in the database

EXEC sp_MSforeachtable '
RAISERROR(''Started dropping table ?'',1,1)
DROP TABLE ?'
(Visited 1,108 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (2)

Thanks,

if I am using the following script:

EXEC sp_MSForEachTable ‘Print “Rebuild index on: ?”; ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90);’

1. I would like to specify only one table or a range of tables. how do I modify the following script
2. How do I modify the script to display each rebuild as it is done?
Your feedback is appreciated. Thanks, Dimssy

Write a comment



Tags: , , , , , , , , ,