Skip to main content

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

Popular Posts

Ho Ching named 5th most powerful and is mistaken as first lady by Forbes

Forbes named Singapore Prime Minister wife and CEO of Temasek Holdings, Ho Ching, as the 5th most powerful woman in the world. Ho Ching is mistaken as Singapore's first lady! OMG!

I wonder how can Forbes makes such a blunder. For a complete list, refer to here.

How to stop FortiClient from starting automatically?

Installed FortiClient recently but the challenge in disabling the application/service from running automatically on every start-up annoyed me. Attempt to stop 'FortiClient Service Scheduler' only return 'Parameter is incorrect' error message.

An article on Technet help solve my trouble. To stop FortiClient from starting automatically, try the following:
Shut down FortiClient from the system tray.
Run net stop fortishield on command prompt.
Run msconfig.
On msconfig, switch to the Services tab. Clear the FortiClient Service Scheduler check box and click Apply.Run services.msc on command prompt to open up show all available services.Look for FortiClient Service Scheduler. Switch Startup type to Manual.Restart your computer. FortiClient should not be running automatically the next time round. Hope it helps.

China adds Skype onto its "Illegal" list

Skype may soon join Twitter, Facebook, and YouTube on China's Internet blacklist. This is after the Chinese regulator having declared Internet phone services other than those provided by China Telecom and China Unicom as illegal.

Such move was criticized as being used as a measure to protect the duopoly of state-owned telecom carriers.

VoIP decision means Skype now illegal [via]