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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">