Query to get all database name in SQL Server?


I am looking at this article Query in Sql server to list all stored procedures in all databases which give all stored procedures name in all database, so I would like to know SQL query to list all databases of an SQL Instance. As I would be allowing users to show list of databases in Front-end, to select and then get all stored procedures of the database.


Asked by:- bhanu
1
: 254 At:- 10/1/2021 9:15:51 AM
SQL sql list all databases SQL Server get list of databases and sizes






1 Answers
profileImage Answered by:- vikas_jk

To get list of all database using query in SQL Server, use below query

SELECT name FROM master.sys.databases

You will see output like below

sql-list-all-database-sql-server-min.png

If you want list all sql server database name with there size, you can execute query below

    SELECT      sys.databases.name,  
                CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]  
    FROM        sys.databases   
    JOIN        sys.master_files  
    ON          sys.databases.database_id=sys.master_files.database_id  
    GROUP BY    sys.databases.name  
    ORDER BY    sys.databases.name  

Which will give you both database name and there size, as shown below

sql-server-database-size-min.png

Source: Check database size in Sql server ( Various Ways explained)

Another query to list of all database excluding non-user database

SELECT name FROM master.sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

Above query will exclude database name like "master", "tempdb" etc, which aren't created by user.

1
At:- 10/1/2021 9:31:54 AM
Thanks for the prompt reply. 0
By : bhanu - at :- 10/1/2021 9:34:28 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly