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

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.

Use-Cases

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'' 
              BEGIN 
                    UPDATE STATISTICS ? WITH FULLSCAN
               END';

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
                 BEGIN 
                     ALTER INDEX ALL ON ? REBUILD 
                 END';

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