View or Change the Properties of a Database

Artikel
02/28/2023

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

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

In This Topic

Before you begin:

Recommendations

Security
To view or change the properties of a database, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

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.

Using SQL Server Management Studio

To view or change the properties of a database

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand Databases, right-click the database to view, and then click Properties.
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.

Using 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, 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

Connect to the Database Engine and then connect to the database for which you wish to view its properties.
From the Standard bar, click New Query.
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 AdventureWorks2022 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('AdventureWorks2022', 'IsAutoShrink');  
```

To view the properties of a database by querying sys.databases

Connect to the Database Engine and then connect to the database for which you wish to view its properties..
From the Standard bar, click New Query.
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 AdventureWorks2022 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 = 'AdventureWorks2022';  
```

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

Connect to the Database Engine and then connect to the database for which you wish to view its properties..
From the Standard bar, click New Query.
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

Connect to the Database Engine.
From the Standard bar, click New Query.

Copy and paste the following example into the query window. The example determines the state of snapshot isolation on the AdventureWorks2022 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 AdventureWorks2022;
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'AdventureWorks2022';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2022
    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'AdventureWorks2022';
GO

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

Connect to a database in your SQL Server instance.
From the Standard bar, click New Query.
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   
```

Dela via