MSSQL: sp_MSforeachdb to loop through all databases without using cursors

sp_MSforeachdb is an undocumented stored procedure since SQL 2005 that can come in real handy, looping through all databases to get information without having to write cursors. The idea of its usage is to use the [?] placeholder and SQL will intelligently do a replace with a database name.

Just some sample usage of sp_MSforeachdb.

--Get all table/columns information from all databases
sp_MSforeachdb 'SELECT * FROM [?].[INFORMATION_SCHEMA].[COLUMNS]'

--Check for orphans login
EXECUTE sp_MSforeachdb 'sp_change_users_login @Action=''report'''

--Get database information - space, name
EXECUTE sp_MSforeachdb 
 'SELECT
  ServerName=@@SERVERNAME, DBName=DB_NAME(), [fileid], CONVERT(DECIMAL(12,2),ROUND([size]/128.000,2)) AS ''SizeMB'',
  SpaceUsedMB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY([name],''SpaceUsed'')/128.000,2)),
  FreeSpaceMB = CONVERT(DECIMAL(12,2),ROUND(([size]-FILEPROPERTY([name],''SpaceUsed''))/128.000,2)) ,
  name, filename
  FROM dbo.sysfiles'

Undocumented may mean it may not be supported in future versions. My last check is sp_MSforeachdb still works for SQL 2008.

Related reading:

1. MSSQL: sp_MSforeachtable to loop through all tables without using cursors