Get List of All Databases from SQL Server

In this short quick post we will learn how we can get the list of all user Database from SQL Server. SQL Server provided different system views those can be used to get information about objects present in SQL Server. To get list of all databases we can use sys.databases view that provides name of databases with other properties such as recovery model, state_desc,compatibility level etc.

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

--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