Edit

View or change the properties of a database

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article describes how to view or change the properties of a database in SQL Server with SQL Server Management Studio or Transact-SQL. After you change a database property, the modification takes effect immediately.

Recommendations

  • When AUTO_CLOSE is ON, some columns in the sys.databases catalog view and DATABASEPROPERTYEX function return NULL because the database is unavailable to retrieve the data. To resolve this issue, open the database.

Permissions

To change the properties of a database, you need ALTER permission on the database. To view the properties of a database, you need at least membership in the public fixed database role.

Use SQL Server Management Studio

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand Databases, right-click the database to view, and then select Properties.

  3. In the Database Properties dialog box, select a page to view the corresponding information. For example, select the Files page to view data and log file information.

Use Transact-SQL

Transact-SQL provides a number of different methods for viewing the properties of a database and for changing the properties of a database. To view the properties of a database, use the DATABASEPROPERTYEX function and the sys.databases catalog view. To change the properties of a database, use the version of the ALTER DATABASE statement for your environment: ALTER DATABASE or ALTER DATABASE. To view database scoped properties, use the sys.database_scoped_configurations catalog view. To alter database scoped properties, use the ALTER DATABASE SCOPED CONFIGURATION statement.

View a property of a database with the DATABASEPROPERTYEX function

  1. Connect to the Database Engine and then connect to the database for which you want to view its properties.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example uses the DATABASEPROPERTYEX system function to return the status of the AUTO_SHRINK database option in the AdventureWorks2025 database. A return value of 1 means that the option is set to ON, and a return value of 0 means that the option is set to OFF.

    SELECT DATABASEPROPERTYEX('AdventureWorks2025', 'IsAutoShrink');

View the properties of a database by querying sys.databases

  1. Connect to the Database Engine and then connect to the database for which you want to view its properties.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example queries the sys.databases catalog view to view several properties of the AdventureWorks2025 database. This example returns the database ID number (database_id), whether the database is read-only or read-write (is_read_only), the collation for the database (collation_name), and the database compatibility level (compatibility_level).

    SELECT database_id,
       is_read_only,
       collation_name,
       compatibility_level
FROM sys.databases
WHERE name = 'AdventureWorks2025';

View the properties of a database-scoped configuration by querying sys.databases_scoped_configuration

  1. Connect to the Database Engine and then connect to the database for which you want to view its properties.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example queries the sys.database_scoped_configurations catalog view to view several properties of the current database.

    SELECT configuration_id,
       name,
       value,
       value_for_secondary
FROM sys.database_scoped_configurations;

    For more examples, see sys.database_scoped_configurations.

Change the properties of a SQL Server database with ALTER DATABASE

  1. Connect to the Database Engine.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window. The example checks the state of snapshot isolation on the AdventureWorks2025 database, changes the state of the property, and then verifies the change.

    To check the state of snapshot isolation, select the first SELECT statement and select Execute.

    To change the state of snapshot isolation, select the ALTER DATABASE statement and select Execute.

    To verify the change, select the second SELECT statement, and select Execute.

    USE AdventureWorks2025;
GO

-- Check the state of the snapshot_isolation_framework in the database.
SELECT name,
       snapshot_isolation_state,
       snapshot_isolation_state_desc AS [Description]
FROM sys.databases
WHERE name = N'AdventureWorks2025';
GO

USE master;
GO

ALTER DATABASE AdventureWorks2025
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Check again.
SELECT name,
       snapshot_isolation_state,
       snapshot_isolation_state_desc AS [Description]
FROM sys.databases
WHERE name = N'AdventureWorks2025';
GO

Change the database-scoped properties with ALTER DATABASE SCOPED CONFIGURATION

  1. Connect to a database in your SQL Server instance.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window. The following example sets MAXDOP for a secondary database to the value for the primary database.

    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
    SET MAXDOP = PRIMARY;

Related content

  • Last updated on