Executing a single SQL query against all tables in your database using the sp_MSforeachtable stored procedure

Today, let's talk about an undocumented SQL Server feature that can be quite handy at times: the "sp_MSforeachtable" stored procedure. It can run a command once per each table in the database.

Example: update statistics for every table:

EXEC sys.sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';

Example: print row counts (prints table name, then count):

EXEC sys.sp_MSforeachtable 'PRINT ''?''; SELECT COUNT(*) AS rows FROM ?;';

Example: run the same SELECT against each table that has a specific column:

EXEC sys.sp_MSforeachtable '
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''?'') AND name = ''IsActive'')
    SELECT ''?'' AS [table], COUNT(*) AS active_rows FROM ? WITH (NOLOCK) WHERE IsActive = 1;
';

If you prefer a documented approach, generate the commands from sys.tables and execute them:

DECLARE @sql nvarchar(max) = N'';
SELECT @sql = @sql + N'UPDATE STATISTICS ' 
    + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' WITH FULLSCAN;'
    + CHAR(13)
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id;

EXEC sp_executesql @sql;

Quick, scriptable, and easy to revert in source control. Hope this helps!