How to View SQL Server Database Properties

Native Auditing

Netwrix Auditor for SQL Server

Steps

Start Microsoft SQL Server Management Studio (MSSMS).
On the File menu, clickConnect Object Explorer. Fill out the Connect to Server dialog box:

In the Server type list box, select Database Engine.
In the Server name text box, type the name of the SQL cluster server.
In the Authentication list box, choose your SQL Server Authentication method and specify your credentials. If you do not want to re-type the password every time you connect to the server, tick Remember password.

Click Connect.
Upon connection, click New Query and paste the following script into the query field:

SELECT
       sys.databases.name as 'Database Name',
       sys.databases.user_access_desc as "Restrict Access",
       sys.databases.state_desc as 'State',
       sys.databases.is_read_only as 'Read Only',
       sys.databases.is_auto_shrink_on 'Auto Shrink Enabled',
       sys.databases.is_encrypted as 'Encrypted',
       bckup.last_backup as 'Last Database Backup',
       files.data_file as 'Database File Path',
       files.log_file as 'Log File Path',
       files.db_size as 'Database Size MB',
       files.log_size as 'Log File Size MB',
       sys.databases.database_id as 'Database ID'
FROM sys.databases
LEFT JOIN (
       SELECT msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_backup
       FROM msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
       WHERE msdb..backupset.type = 'D'
       GROUP BY msdb.dbo.backupset.database_name
       ) bckup on bckup.database_name = sys.databases.name
LEFT JOIN (
              SELECT
                      mdf.database_id,
                      mdf.name,
                      mdf.physical_name as data_file,
                      ldf.physical_name as log_file,
                      db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
                      log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
              FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf
              JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf
              ON mdf.database_id = ldf.database_id
       ) files
ON files.database_id = sys.databases.database_id
ORDER BY 'Database Name'

Click Execute (or hit the F5 key).
Review the list of databases and their properties in the query results:

Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server — State-in-Time -> SQL Server Databases.
Click View to see your report:

View SQL Server database properties with a single click.

Gathering database properties (also called database options) is a regular database administration task. It is critical for ensuring your databases remain in line with your default baseline settings, as well as monitoring their vital signs to ensure strong performance and availability.

With SQL Server Management Studio, you can obtain information about SQL Server database propertiesusing either the graphical interface or your own queries. However, no matter which option you pick, the process isn’t always be as user-friendly as you’d probably like. For example, when using GUI tools, you cannot easily get just the information you need, and you can only view one database at a time. With Transact-SQL commands such as sp_helpdb, all SQL database parameters are linked to each other, rather than listed individually. When querying system tables, you often need to join multiple tables to get the results you want, and then transform the results (converting different data types to the desired format) to understand them. Moreover, you can get various database options (such as recovery model, auto close, auto shrink, compatibility level, collation name and snapshot isolation) by querying sys.databases, but if a database is in OFFLINE status and you have limited privileged, you won’t see any data — the user must have at least the ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission in the master database.

SQL Server does have a built-in DATABASEPROPERTYEX function that will return specific information for one or all databases, but to get the database files’ physical size, you’ll have to query each different database. In short, understanding the configuration of your SQL Server database properties using native tools can be quite time-consuming and require significant expertise.

Using Netwrix Auditor for SQL Server you can get a clear summary of your database options in a single click. Plus, you can subscribe to the report and have it delivered automatically to your inbox on the schedule you choose. You can even easily set up alerts on configuration changes, so you’ll know right away when you need to take action to prevent downtime or other problems that could impact the business.

Previous How-to

How to View SQL Server Database File Locations

Next How-to

How to List User Roles in SQL Server