Get List of All Databases from SQL Server

Using T-SQL Scripts or SQL Server Management Studio, this post explains how to get a list of databases on a SQL Server instance.

The first four database are system database, the below query uses database_id>4, that will give us only user databases.

Use T-SQL Scripts

--Get List of User Database from SQL Server Instance
SELECT * FROM sys.databases
WHERE database_id>4

We can use these views to generate different type of queries. In below query I used sys.databases view to generate backup scripts for all the user databases.

--Generate Backup Scripts for all the user databases from sys.Databases view
SELECT 'BACKUP DATABASE ['+name+'] TO  DISK = N''C:\'+name+'_'+REPLACE(CAST(CAST(GETDATE() AS Date) AS VARCHAR(10)),'-','_') +'.bak''
WITH NOFORMAT, NOINIT,  NAME = N'''+name+'-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
FROM sys.databases
WHERE database_id>4      

Use SQL Server Management Studio

To view a list of databases on an instance of SQL Server

1.     In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2.     To see a list of all databases on the instance, expand Databases.