View or Change the Properties of a Database

Article
06/13/2017

This topic describes how to view or change the properties of a database in SQL Server 2014 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, execute a USE statement to open the database.

Security

Permissions

Requires ALTER permission on the 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

To view a property of a database by using DATABASEPROPERTYEX

Connect to the Database Engine.
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 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.

USE AdventureWorks2012;  
GO  
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsAutoShrink');  
GO

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

Connect to the Database Engine.
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 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).

USE AdventureWorks2012;  
GO  
SELECT database_id, is_read_only, collation_name, compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
GO

To change the properties of a 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 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

Share via