Sunday, March 10, 2013

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

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

--Get database information - space, name
EXECUTE sp_MSforeachdb 
  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

1 comment:

  1. anda ingin pemasukkan lebih selama masa pandemi ini, cukup dengan mainkan slot online dan hasilkan uang hingga jutaan rupiah. klik


Do provide your constructive comment. I appreciate that.