How to List all Databases in a SQL Server Instance

To list all databases in a SQL Server Instance, run this query:

SELECT name as Database_Name
FROM SYS.DATABASES

list_all_databases_in_a_sql_server_instance

To list all OFFLINE Databases:

SELECT name, state_desc
FROM SYS.DATABASES
WHERE state_desc = 'OFFLINE'

list_all_databases_in_a_sql_server_instance_offline

Read related blog post:
List Databases that have a different Collation than the SQL Server Instance

To list all databases in a SQL Server Instance, ordered by size, we will first create a temporary table #db_details, insert the results of the sp_helpdb System Stored Procedure into the temporary table, and then sort the result by database size:

CREATE TABLE #db_details (name nvarchar(128), db_size nvarchar(50),owner nvarchar(128),db_id int,created varchar(128),status nvarchar(2000),compatibility_level int)

INSERT INTO #db_details EXEC sp_helpdb
SELECT *
FROM #db_details
ORDER BY CONVERT (DECIMAL, REPLACE (db_size, 'MB', '')) DESC

DROP TABLE #db_details

list_all_databases_in_a_sql_server_instance_size

Using the same method, you can list all databases in a SQL Server Instance, that have the same owner, for example 'sa':

CREATE TABLE #db_details (name nvarchar(128), db_size nvarchar(50),owner nvarchar(128)
,db_id int,created varchar(128),status nvarchar(2000),compatibility_level int)

INSERT INTO #db_details EXEC sp_helpdb
SELECT *
FROM #db_details
WHERE owner ='sa'

DROP TABLE #db_details