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:
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 = '#'
Thank you. This is exactly what I was looking for!
Post a Comment