Monday, March 22, 2010

Using sp_MsForEachDb and sp_MsForEachTable Together

Ever wanted to run a T-SQL command on every user table in every user database? Here's how to nest the undocumented sp_MsForEachTable system stored procedure inside the undocumented sp_MsForEachDB system stored procedure.

DECLARE @Sql NVARCHAR(4000) = 
      'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''''#'''' AND owner_sid = 0x01) RETURN '
    + 'RAISERROR(''''Reclaiming space on table ' + QUOTENAME('#') + '.?'''', 10, 1) WITH NOWAIT '
    + 'USE ' + QUOTENAME('#') + ' '
    + 'DBCC CLEANTABLE(''''#'''', ''''?'''', 10000) WITH NO_INFOMSGS '

SET @Sql = 'USE ' + QUOTENAME('#') + ' EXEC sp_MsForEachTable ''' + @Sql + ''''

EXEC sp_MsForEachDb @Sql, @replacechar = '#'

The trick is the @replacechar parameter to the sp_MsForEachDb stored procedure: it's how we keep the placeholders for the database and the table separate. If there's an easier way, I'd love to see it.

2 comments:

banawr said...

Great, that is just what I was looking for!
below you can find my own example: rebuild ALL index on ALL tables of ALL database.

exec sp_msforeachdb @command1='
use #;
if ''#'' not in (''master'', ''model'', ''msdb'', ''tempdb'')
begin
--print ''#''
exec sp_MSforeachtable @command1=''
ALTER INDEX all ON ? rebuild WITH (FILLFACTOR = 90, sort_in_tempdb = ON);
''
end
', @replacechar = '#'

Anonymous said...

Thank you. This is exactly what I was looking for!

Post a Comment