Browse FAQs

Obtaining Information about all Databases in a SQL Server Instance

Published date: 11 March 2019

Issue
List of databases and their general settings.

Product Line
SQL Server

Environment
SQL Server Management Studio (SSMS)

Cause
For troubleshooting it may be necessary to look into SSMS in a broader detail. This query provides one row for each database in an instance of SQL Server, including system databases such as Master, Model, MSDB and TempDB.

Resolution
The database that contains information about all databases in an instance of SQL Server is sys.databases.  A select statement is required to obtain the result:

select * from sys.databases

Since all columns may not provide useful information for a specific problem, it may be better to restrict the number of columns by specifying which ones to be displayed.  Here is an example:

select name, compatibility_level, state_desc, recovery_model_desc, page_verify_option_desc
from sys.databases


         


Click here to get a list of all columns with a description of each for SQL Server 2017 along with some examples.

 

Was this helpful?

What can we do to improve the information ?

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.