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