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.
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
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
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.
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly