What if we want to run sp_msforeachtable for all databases together ?

First ,here are few msforeach commands that you may find useful. :


sp_autostats for each table in a database :
EXEC sp_MSforeachtable @command1="print '?' EXEC sp_autostats '?'"

Reindexing each index in a database :
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Index fragmentation for each index in a database :
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

Space used by each table in a database :
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Updating statistics in a database :
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Disabling triggers for a database :
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

finding total number of rows in all the tables in a database :
EXEC sp_msforeachtable 'select count (*) as ''?'' from ?'

to detach all the user databases :
select 'EXEC sp_detach_db @dbname = N'''+RTRIM ( name)+''','''+'true''' from sys.sysdatabases where dbid>4 and name not like 'distribution'


You know the object name, but don't know in which database it is.Run the following query in Text mode :

sp_msforeachdb 'use ?;print "?";select name from sys.sysobjects where name='''''


Now , lets say you need to run them for each database ..but you cannot expect it to run all together for all the databases just like sp_msforeachdb..

Lets try this way :
DECLARE @SQL NVarchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
PRINT @SQL

Here , what we are doing is initialising @sql with nothing .then first time it will get master.So it will put master plus the remaining string in @sql ...ie. 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13).
So , in first flow @ will contain
EXEC master..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
In the second round it will have the previous string in @sql plus the same string for the next database name.To check this , just remove "@SQL+" stuff and run this script .You will see the string for the last DBID because each time the value in @SQL was overwritten :)..
Hence , this string will keep on increasing till the number of databases count ends ..So , you may have to increase the nvarchar limit if therer are a lot many databases ..

I have also copied few MSxxxxx stuff for your referance:

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
EXEC sp_MShelpcolumns 'Production.products'
EXEC sp_MSindexspace 'Production.products'
EXEC sp_MStablespace 'Production.products'

Hope this helps :D