Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Three Way To List All Databases On Your Server
From Wiki
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
SQL Server 2000/2005
- SELECT name FROM master..sysdatabases
- WHERE name NOT IN('master','msdb','tempdb','model')
- ORDER BY Name
SQL Server 2005 only
- SELECT name
- FROM sys.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
Contributed by: --SQLDenis 15:54, 6 June 2008 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.