SQL Server - sp_MSforeachtable

26 August,2010 by Jack Vamvas

This is an undocumented stored procedure. It's found in the master db and accepts the following input parameters.It returns one (or greater ) rows for every table. Each table has it's own resultset .

I find it useful for managing a process such as a sql server rapid tuning for an emergency performance issue

 

--Input parameters :

@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null, 
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, 
@postcommand nvarchar(2000) = null


--An example:

exec sp_MSforeachtable 
 @command1 = 'print ''Processing table ?''',
 @command2 = 'UPDATE STATISTICS ? WITH FULLSCAN' ,
 @command3 = 'print ''Processed table ?'''

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - sp_MSforeachtable


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer