Three Way To List All Databases On Your Server

Sometimes you want to print out a document with all the databases that exists on one server so that you can compare this against another server for example
Listed below are three ways to accomplish this by using a system table, a system view and an undocumented stored procedure

First Way
Use the INFORMATION_SCHEMA.SCHEMATA system view

SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN('master','msdb','tempdb','model')
ORDER BY CATALOG_NAME

Second Way
Use the sysdatabases system table in the master database

SELECT name FROM master..sysdatabases
WHERE name NOT IN('master','msdb','tempdb','model')
ORDER BY Name

Third way
Use the undocumented sp_MSForEachDB procedure

CREATE TABLE #AllDB (Name VARCHAR(100))
INSERT INTO #AllDB
EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
SELECT ''?'' '
SELECT * FROM #AllDB