top of page

Utilizing Conditional Logic with `sp_MSforeachtable`

The `sp_MSforeachtable` and sp_MSforeachDB stored procedures in SQL Server are undocumented stored procedures for performing operations on every table in every database. You can leverage conditional clauses to customize these operations based on specific criteria. Here are different use cases and tips for using conditional clauses with `sp_MSforeachtable` and sp_MSforEachDB

Conditional Logic sp_msforeachtable

When using the sp_MSforeachtable stored procedure, you often need to apply conditional logic to selectively perform operations on tables based on specific criteria. Conditional clauses allow you to fine-tune your operations, ensuring they are only executed on tables that meet certain conditions.


Updating Statistics for Specific Tables:

You can update statistics only for tables with a specific prefix or suffix in their names.

EXEC sp_MSforeachtable 
 @command1 = 'IF OBJECT_NAME(OBJECT_ID(''?'', ''U'')) LIKE ''%tbl'' 

Rebuilding Indexes on Large Tables Only

Details on rebuilding indexes only for tables with a large number of rows

EXEC sp_MSforeachtable 
    @command1 = 'DECLARE @RowCount INT;
                 SELECT @RowCount = COUNT(*) FROM ?;
                 IF @RowCount > 10000000000000
                     ALTER INDEX ALL ON ? REBUILD 

To use sp_MSforeachtable with a conditional clause to filter tables based on their names, such as those containing "EMPLOYEE", you can use the @whereand parameter to apply the filter condition. This allows you to only execute the command on tables whose names match the specified pattern.

EXEC [sp_MSforeachtable]@command1=N'
 PRINT N''?''
 SELECT ''?'',COUNT(*) FROM ?',
 @whereand = N'AND o.[name] LIKE N''%EMPLOYEE%'''

54 views0 comments


bottom of page