View or Change the Properties of a Database

View or Change the Properties of a Database

 

Updated: January 18, 2016

Applies To: SQL Server 2016

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

In This Topic

Recommendations

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

Security

Permissions

Requires ALTER permission on the database to change the properties of a database. Requires at least membership in the Public database role to view the properties of a database.

To view or change the properties of a database

  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 click 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.

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

To view a property of a database by using the DATABASEPROPERTYEX function

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

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example uses the DATABASEPROPERTYEX system function to return the status of the AUTO_SHRINK database option in the AdventureWorks2012 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('AdventureWorks2012', 'IsAutoShrink');  
    
    

To 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 wish to view its properties..

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example queries the sys.databases catalog view to view several properties of the AdventureWorks2012 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 = 'AdventureWorks2012';  
    
    

To 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 wish to view its properties..

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example queries the sys.database_scoped_configurations (Transact-SQL) 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 (Transact-SQL)

To change the properties of a SQL Server 2016 database using ALTER DATABASE

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

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

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

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

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

    USE AdventureWorks2012;
    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'AdventureWorks2012';
    GO
    USE master;
    GO
    ALTER DATABASE AdventureWorks2012
        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'AdventureWorks2012';
    GO
    

To change the database-scoped properties using ALTER DATABASE SCOPED CONFIGURATION

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

  2. From the Standard bar, click 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   
    
    

sys.databases (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
ALTER DATABASE (Transact-SQL)
ALTER DATABASE (Azure SQL Database)
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
sys.database_scoped_configurations (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft