How to get a list of all databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

How to get a list of all databases

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 08:56:57

This piece of code will get you a list of all databases in current server instance.

IF CONVERT(VARCHAR(3), SERVERPROPERTY('ProductVersion')) = '10.'
	SELECT	Name AS CATALOG_NAME
	FROM	sys.databases
	WHERE	Name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Resource')
ELSE IF CONVERT(VARCHAR(2), SERVERPROPERTY('ProductVersion')) = '9.'
	SELECT	Name AS CATALOG_NAME
	FROM	sys.databases
	WHERE	Name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Resource')
ELSE IF CONVERT(VARCHAR(2), SERVERPROPERTY('ProductVersion')) IN ('8.', '7.')
	SELECT	DISTINCT
		CATALOG_NAME
	FROM	INFORMATION_SCHEMA.SCHEMATA
	WHERE	CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb')
ELSE	SELECT	NULL AS CATALOG_NAME
	WHERE	1 = 0