Skip to content

view-or-change-the-properties-of-a-database.md

Latest commit

 

History

History
116 lines (75 loc) · 5.74 KB

view-or-change-the-properties-of-a-database.md

File metadata and controls

116 lines (75 loc) · 5.74 KB
title View or Change the Properties of a Database | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology supportability
ms.topic conceptual
helpviewer_keywords
displaying databases
database viewing [SQL Server]
databases [SQL Server], viewing
viewing databases
ms.assetid 9e8ac097-84b7-46c7-85e3-c1e79f94d747
author stevestein
ms.author sstein
manager craigg

View or Change the Properties of a Database

This topic describes how to view or change the properties of a database in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. After you change a database property, the modification takes effect immediately.

In This Topic

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

  1. In Object Explorer, connect to an instance of the [!INCLUDEssDEnoversion], 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.

Using Transact-SQL

To view a property of a database by using DATABASEPROPERTYEX

  1. Connect to the [!INCLUDEssDE].

  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 [!INCLUDEssSampleDBobject] 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

  1. Connect to the [!INCLUDEssDE].

  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 [!INCLUDEssSampleDBobject] 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

  1. Connect to the [!INCLUDEssDE].

  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 [!INCLUDEssSampleDBobject] 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.

[!code-sqlDatabaseDDL#AlterDatabase9]

See Also

sys.databases (Transact-SQL)
ALTER DATABASE SET HADR (Transact-SQL)
ALTER DATABASE SET Options (Transact-SQL)
ALTER DATABASE Database Mirroring (Transact-SQL)
ALTER DATABASE Compatibility Level (Transact-SQL)
ALTER DATABASE File and Filegroup Options (Transact-SQL)