探索SQL Server数据库的DATABASEPROPERTYEX（）函数

In this article, we explore a SQL function to extract SQL database metadata using the DATABASEPROPERTYEX function.

在本文中，我们探索使用DATABASEPROPERTYEX函数提取SQL数据库元数据SQL函数。

介绍 (Introduction)

We might want to retrieve the database property value. You can connect to SQL Instance using SSMS and get these configurations from the property window. You might not want to use GUI in case you need to check the property for multiple databases. It is possible but time-consuming. You cannot export the data in an excel format as well, and it again causes issues to write down values manually:

我们可能想要检索数据库属性值。 您可以使用SSMS连接到SQL实例，并从属性窗口中获取这些配置。 如果您需要检查多个数据库的属性，则可能不想使用GUI。 可能但很费时。 您也不能以excel格式导出数据，这再次导致手动写入值的问题：

SQL Server provides built-in function DATABASEPROPERTY and DATABASEPROPERTYEX() to retrieve the information. It is essential to know the difference in these functions before proceeding further.

SQL Server提供了内置函数DATABASEPROPERTY和DATABASEPROPERTYEX（）来检索信息。 在继续进行之前，必须了解这些功能的差异。

SQL Server 2012 introduced a new metadata function DATABASEPROPERTYEX. An earlier version of it, DATABASEPROPERTY, is still available but mainly for the backward compatibility. The main issue with DATABASEPROPERTY is that it returns only INT data type in the output. We get sql_variant data type output in the new function DATABASEPROPERTYEX.

SQL Server 2012引入了新的元数据函数DATABASEPROPERTYEX 。 它的早期版本DATABASEPROPERTY仍然可用，但主要是为了向后兼容。 DATABASEPROPERTY的主要问题是它在输出中仅返回INT数据类型。 我们在新函数DATABASEPROPERTYEX中获得sql_variant数据类型输出。

Once we write this function in SSMS, you get information about its argument and return values.

一旦在SSMS中编写了此函数，您将获得有关其参数和返回值的信息。

It requires two arguments:

它需要两个参数：

• Database Name: Specify the database name for which we want to retrieve the information 数据库名称：指定我们要检索其信息的数据库名称
• Property Name: Specify the property name to get its configured value in the database specified in the first argument 属性名称：指定属性名称以在第一个参数指定的数据库中获取其配置值

Let’s explore this built-in function using examples.

让我们使用示例探索这个内置函数。

示例1：获取数据库排序规则 (Example 1: Get database collation )

SQL Server collation is a set of characters and encoding rules. You might have a specific collation for a database depending upon the requirement.

SQL Server排序规则是一组字符和编码规则。 您可能需要根据数据库对数据库进行特定的排序规则。

参数：归类 (Argument: Collation)

Select DATABASEPROPERTYEX('AdventureWorks','Collation') as DBCollation

In this case, the [AdventureWorks] database has a collation SQL_Latin1_General_CP1_CI_AS.

在这种情况下，[AdventureWorks]数据库具有排序规则SQL_Latin1_General_CP1_CI_AS 。

If we do not specify a database name in the first argument, it does not give any error message. You get NULL in the output.

如果我们在第一个参数中未指定数据库名称，则不会给出任何错误消息。 您在输出中得到NULL。

示例2：使用DATABASEPROPERTYEX（）函数检查数据库状态 (Example 2: Check database status using DATABASEPROPERTYEX() function )

We can have different statuses of a SQL database, as specified below:

我们可以指定SQL数据库的不同状态，如下所示：

• Online: It shows that the database is available for queries 在线 ：表明该数据库可用于查询
• Offline: Sometimes, we do not want database available for users, but it should exist in the SQL instance. In this case, we can set the database to offline status 脱机 ：有时，我们不希望数据库可供用户使用，但它应存在于SQL实例中。 在这种情况下，我们可以将数据库设置为脱机状态
• Restoring: We can see restoring status if a database is being restored from the backup, or we have not performed the recovery on a database 恢复：如果正在从备份中恢复数据库，或者我们尚未对数据库执行恢复，则可以看到恢复状态
• Suspect: In case of any issues with the database such as Primary or secondary data file corruption, you can see the database in the suspect mode 可疑：如果数据库出现任何问题，例如主数据文件或辅助数据文件损坏，您可以在可疑模式下查看数据库
• Recovering: Once the database is performing a recovery process, you can see the database in recovery status. You might see this message in case you restart SQL Server while any active transactions are running 恢复：数据库执行恢复过程后，您可以看到数据库处于恢复状态。 如果在任何活动事务运行时重新启动SQL Server，则可能会看到此消息

You can go through an article Understanding different SQL Server database states for more details.

您可以阅读文章了解不同SQL Server数据库状态以获取更多详细信息。

参数：状态 (Argument: Status)

Select DATABASEPROPERTYEX('AdventureWorks','Status') as DBStatus

As shown here, the database [AdventureWorks] database is in ONLINE status.

如此处所示，数据库[AdventureWorks]数据库处于联机状态。

示例3：使用DATABASEPROPERTYEX（）函数检查数据库用户访问 (Example 3: Check database user access using DATABASEPROPERTYEX() function )

Usually, a database remains in a multi-user mode. In this mode, many users can connect to the database and execute queries.

通常，数据库保持在多用户模式。 在这种模式下，许多用户可以连接到数据库并执行查询。

We can also set a database in a single user mode. In this mode, the only user with db_owner, dbcreator, or sysadmin can connect to a database, but only a single database connection is allowed.

我们还可以在单​​用户模式下设置数据库。 在这种模式下，只有具有db_owner，dbcreator或sysadmin的用户才能连接到数据库，但是只允许一个数据库连接。

Apart from these modes, we can set a database in the RESTRICTED_USER mode. In this mode, only users with db_owner, dbcreator, and sysadmin roles can connect to the database.

除了这些模式，我们还可以将数据库设置为RESTRICTED_USER模式。 在这种模式下，只有具有db_owner，dbcreator和sysadmin角色的用户才能连接到数据库。

参数：UserAccess (Argument: UserAccess)

Select DATABASEPROPERTYEX('AdventureWorks','UserAccess') as [DBUserAccess]

We have a database [AdventureWorks] in the multi-user mode.

在多用户模式下，我们有一个数据库[AdventureWorks]。

示例4：使用DATABASEPROPERTYEX（）函数检查SQL数据库的自动关闭属性状态 (Example 4: Check Auto Close property status of a SQL database using DATABASEPROPERTYEX() function )

We can set a database to close automatically using the AUTO_CLOSE property set to true. In this case, the database shuts down and frees system resources once the last user exits.

我们可以使用设置为true的AUTO_CLOSE属性将数据库设置为自动关闭。 在这种情况下，一旦最后一个用户退出，数据库将关闭并释放系统资源。

For this example, let’s turn on AUTO_CLOSE property on my test database [Showroom] using the below script.

对于此示例，让我们使用以下脚本在测试数据库[ Showroom ]上打开AUTO_CLOSE属性。

USE [master]
GO
ALTER DATABASE [Showroom] SET AUTO_CLOSE ON

Now, we check the value for AUTO_CLOSE using the argument IsAutoClose in the [Showroom] and [AdventureWorks] databases.

现在，我们在[ Showroom ]和[ AdventureWorks ]数据库中使用参数IsAutoClose检查AUTO_CLOSE的值。

Select DATABASEPROPERTYEX('Showroom','IsAutoClose') as [Auto_Close]
Select DATABASEPROPERTYEX('Master','IsAutoClose') as [Auto_Close]

In the below screenshot, we verify that the [Showroom] database is set to auto close whereas [AdventureWorks] database does not have this property enabled:

在下面的屏幕截图中，我们确认[ Showroom ]数据库已设置为自动关闭，而[ AdventureWorks ]数据库未启用此属性：

示例5：使用DATABASEPROPERTYEX（）函数检查SQL数据库的自动创建和自动更新统计信息属性状态 (Example 5: Check Auto-Create and Auto-update Statistics property status of a SQL database using DATABASEPROPERTYEX() function)

( )

SQL Server query optimizer creates the statistics automatically based on the query predicates. We can see statistics starting from _WA_sys name.

SQL Server查询优化器根据查询谓词自动创建统计信息。 我们可以看到以_WA_sys名称开头的统计信息。

• You can refer to the SQL Server Statistics and how to perform Update Statistics in SQL article for SQL Server statistics.

  有关 SQL Server统计信息， 请参阅 SQL 中的 SQL Server统计信息以及如何执行更新统计 信息。

参数：IsAutoCreateStatistics (Argument: IsAutoCreateStatistics)

Select DATABASEPROPERTYEX('Showroom','IsAutoCreateStatistics') as [Auto Create Statistics]

参数：IsAutoUpdateStatistics (Argument: IsAutoUpdateStatistics)

We can control the query optimizer behavior to update SQL Server statistics automatically based on a predefined threshold. You should always set this property enabled unless you have a specific reason to disable auto-update statistics.

我们可以控制查询优化器的行为以基于预定义的阈值自动更新SQL Server统计信息。 除非有特殊原因要禁用自动更新统计信息，否则应始终将此属性设置为启用。

Select DATABASEPROPERTYEX('Showroom','IsAutoUpdateStatistics') as [Auto Update Statistics]

In the output, value 1 shows that auto-update statistic is turned on for [Showroom] database:

在输出中，值1显示[ Showroom ]数据库的自动更新统计信息已打开：

示例6：检查数据库是否是SQL数据库的仅架构和统计信息副本 (Example 6: Check whether the database is a schema and statistics only copy of a SQL database)

SQL Server 2014 SP2 onwards, we can create a blank database with the schema-and statistics-only copy of a user database using DBCC CLONEDATABASE. We can use DATABASEPROPERTYEX to check whether it is a clone database or not.

从SQL Server 2014 SP2开始，我们可以使用DBCC CLONEDATABASE使用用户数据库的仅架构和统计信息副本创建空白数据库。 我们可以使用DATABASEPROPERTYEX来检查它是否是克隆数据库。

Select DATABASEPROPERTYEX('AdventureWorks','IsClone') as [DB Clone]

In an earlier example, we set [Showroom] database to auto-close once the last user exits. If we check the clone property for this database, it returns NULL output because It cannot access the database to check its property.

在较早的示例中，我们将[ Showroom ]数据库设置为在最后一个用户退出后自动关闭。 如果我们检查此数据库的clone属性，它将返回NULL输出，因为它无法访问数据库以检查其属性。

参数：IsClone (Argument: IsClone)

Select DATABASEPROPERTYEX('Showroom','IsClone') as [DB Clone]

示例7：检查SQL数据库一致性检查的最后日期和时间 (Example 7: Check last date and time of the consistency check for a SQL database)

We should perform regular consistency checks of all databases in SQL instance using DBCC CHECKDB. It ensures the database is consistent and does not have any corruption. We can check the timestamp of the last successful database consistency check.

我们应该使用DBCC CHECKDB对SQL实例中的所有数据库进行定期的一致性检查。 它确保数据库是一致的，并且没有任何损坏。 我们可以检查上一次成功的数据库一致性检查的时间戳。

参数：LastGoodCheckDbTime (Argument: LastGoodCheckDbTime)

Select DATABASEPROPERTYEX('SQLShack','LastGoodCheckDbTime') 
as [Last Successful DBCC CHECKDB]

In my demo environment, I do not have performed DBCC CHECKDB on the master database. If we have not performed a DBCC CHECKDB on a database, it returns default output 1900-01-01 00:00:00.000

在我的演示环境中，我没有在master数据库上执行DBCC CHECKDB 。 如果我们尚未在数据库上执行DBCC CHECKDB ，它将返回默认输出1900-01-01 00：00：00.000

Select DATABASEPROPERTYEX('Master','LastGoodCheckDbTime') 
as [Last Successful DBCC CHECKDB]

示例8：检查SQL数据库的数据库恢复模型 (Example 8: Check database recovery model for a SQL database)

SQL Server provides Full, Bulk-logged, and Simple recovery model. It determines the kind of database backups and the ability to recover data in case of any issues.

SQL Server提供了完整，批量记录和简单的恢复模型。 它确定数据库备份的类型以及在出现任何问题时恢复数据的能力。

论点：恢复 (Argument: Recovery)

In the below query, we check the recovery model for three databases – Master, SQLShack & Javatest. We see different recovery model set for all databases in a SQL instance.

在下面的查询中，我们检查三个数据库的恢复模型-Master，SQLShack和Javatest。 我们看到在SQL实例中为所有数据库设置了不同的恢复模型。

You can refer to Understanding SQL Server database recovery models to explore recovery models.

您可以参考了解SQL Server数据库恢复模型以探索恢复模型。

Select DATABASEPROPERTYEX('Master','Recovery') as [Master DB Recovery model],
DATABASEPROPERTYEX('SQLShack','Recovery') as [SQLShack DB Recovery model],
DATABASEPROPERTYEX('Javatest','Recovery') as [Javatest DB Recovery model]

示例9：使用DATABASEPROPERTYEX函数获取所有SQL数据库的不同属性的值 (Example 9: Get values for different properties of all SQL databases using DATABASEPROPERTYEX function)

We can go through the Microsoft docs for all arguments supported by the DATABASEPROPERTYEX function. In this example, we want to retrieve the configurations for all SQL databases available in the SQL instance. I found a useful script in Microsoft TechNet for it. I modified this script to include SQL Server 2019 version as well and attached to this article.

我们可以遍历Microsoft文档中DATABASEPROPERTYEX函数支持的所有参数。 在此示例中，我们要检索SQL实例中可用的所有SQL数据库的配置。 我在Microsoft TechNet中找到了一个有用的脚本。 我修改了此脚本以使其也包括SQL Server 2019版本，并随附于本文。

This script uses a Case statement in SQL Server. You can refer to the article CASE statement in SQL to learn it. It also gets the database name for the first argument using the sys.databases system view:

Download the DATABASEPROPERTYEX.sql file

该脚本在SQL Server中使用Case语句。 您可以参考SQL中的CASE语句文章来学习它。 它还使用sys.databases系统视图获取第一个参数的数据库名称：

在 here 此处下载DATABASEPROPERTYEX.sql文件

In the output, we can see different properties returned for all SQL databases using the DATABASEPROPERTYEX function:

在输出中，我们可以看到使用DATABASEPROPERTYEX函数为所有SQL数据库返回的不同属性：

结论 (Conclusion)

In this article, we explored useful DATABASEPROPERTYEX function to check configuration values for SQL databases. It comes as a handy and useful script where you need to check values for all SQL databases available in the instance. We cannot use the GUI method in this case, as it would take a longer time. You can use the DATABASEPROPERTYEX function to check the values, export it to CSV, Excel format, and store it for your records.

在本文中，我们探索了有用的DATABASEPROPERTYEX函数来检查SQL数据库的配置值。 它是一个方便实用的脚本，您需要在其中检查实例中所有可用SQL数据库的值。 在这种情况下，我们将无法使用GUI方法，因为这将花费更长的时间。 您可以使用DATABASEPROPERTYEX函数来检查值，将其导出为CSV，Excel格式，并将其存储以备记录。

翻译自: https://www.sqlshack.com/explore-the-databasepropertyex-function-for-sql-server-databases/