Metadata Functions in SQL Server and Their Use Cases
SQL Server offers various system functions to get metadata for various aspects of SQL Server, databases, and database objects. In this SQL tutorial, I will cover a few of these helpful metadata functions you can use.
The term "metadata" is described as information about data. For example, when you purchase a product, you can learn much about it from the product wrapper, like the price, expiration date, manufactured date, size, etc. This information is like metadata because it is all the information related to that specific product.
Now, let's describe metadata in a SQL Server context. SQL Server stores data and that data serves various business needs. In addition, there is information about that data in SQL Server and that information is known as metadata. SQL Server offers several system functions that will return metadata. There are more than 30 metadata functions in SQL Server.
This tip covers the following metadata functions:
- DB_NAME and DB_ID
- FILE_NAME, FILE_ID and FILE_IDEX
- SCHEMA_NAME and SCHEMA_ID
- OBJECT_NAME and OBJECT_ID
Please see the following link to read more about all metadata functions.
SQL Server SERVERPROPERTY Function
Let's start with one of the more popular metadata functions that most SQL Server professionals use to get SQL Server version details. This function is SERVERPROPERTY and is very popular for DBAs and developers to obtain various properties of the SQL Server instance. This function returns information such as server name, SQL Server edition, product version, if it is clustered, and more. Here is another article that covers more about the SERVERPROPERTY function.
Below is the function with various values that can be returned with this function.
SELECT SERVERPROPERTY('MachineName') AS ComputerName, SERVERPROPERTY('ServerName') AS InstanceName, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Collation') AS Collation, SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly, SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition, SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled, SERVERPROPERTY('IsClustered') AS IsClustered, SERVERPROPERTY('IsBigDataCluster') AS IsBigDataCluster, SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath, SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath, SERVERPROPERTY('InstanceDefaultBackupPath') AS InstanceDefaultBackupPath GO
The output shows the following information.
SQL Server DATABASEPROPERTYEX Function
Now let's look at the DATABASEPROPERTYEX function to return information at the database level, including database properties like recovery, status, autoshrink configuration, replication, etc. We need to specify the database name along with the property name.
This T-SQL query will get the recovery model, current status of the database, and the user access of the specified database for database TESTDB.
SELECT DATABASEPROPERTYEX('TESTDB', 'RECOVERY') AS [Recovery Model], DATABASEPROPERTYEX('TESTDB', 'Status') AS [DB Status], DATABASEPROPERTYEX('TESTDB', 'UserAccess') AS [UserAccess]; GO
The output shows that TESTDB is using the full recovery model, it is online and multiple users can access the database.
SQL Server DB_NAME and DB_ID Functions
This section describes the DB_NAME and DB_ID functions which are used to retrieve the name of the database using the database ID or the database ID using the name of the database. If you do not specify database ID or database name, the output will return information from the database where the query is executed.
The example below demonstrates how to specify a database ID of a user database to return the name of the database and again specify the database name to get the ID of the same database.
USE TESTDB GO SELECT DB_NAME() AS [DB Name], DB_ID() AS [DB ID] GO SELECT DB_NAME(7) AS [DB Name], DB_ID('TESTDB') AS [DB ID]
The result of both statements is the same.
We can also use the above function with other metadata functions. Here I have used DB_NAME and DATABASEPROPERTYEX functions together to get the information about database TESTDB.
USE TESTDB GO SELECT DB_NAME() AS [DB Name], DATABASEPROPERTYEX(DB_NAME(), 'RECOVERY') AS [Recovery Model], DATABASEPROPERTYEX(DB_NAME(), 'Status') AS [DB Status], DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS [UserAccess], DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS [Collation] GO
The output has returned useful information about the user database, TESTDB, stating that it is ONLINE and open for MULTI USERS, recovery model is set as FULL, and collation as SQL_Latin1_General_CP1_CI_AS.
SQL Server FILE_NAME, FILE_ID, and FILE_IDEX Functions
The following metadata functions are related to file-level information:
The FILE_NAME function returns a logical file name for a specified database file ID, whereas the FILE_ID function will return the logical file ID for a specified database file name.
Microsoft introduced a new function named FILE_IDEX that can return the logical file ID of a specified database file name because the FILE_ID function is going to be removed from future versions of SQL Server. You should use FILE_IDEX in your development work in place of the FILE_ID function moving forward.
This example executes sp_helpdb on the user database TESTDB to return all its information at the file level. We can see the file names, IDs, and other details in its output. Next, I will use the file ID from this output to show the FILE_NAME function. I will specify the file ID in this function and return the name of the database file name.
EXEC sp_helpdb 'TESTDB' GO SELECT FILE_NAME(1) AS 'File Name 1', FILE_NAME(2) AS 'File Name 2'; GO
Have a look at the output. The FILE_NAME function returned the same result as sp_helpdb.
Here is another example.
USE TESTDB GO SELECT FILE_ID('TESTDB')AS [Data File ID], FILE_ID('TESTDB_log') AS [Log File ID] GO USE TESTDB; GO SELECT FILE_IDEX('TESTDB') AS [Data File ID], FILE_IDEX('TESTDB_log') AS [Log File ID]; GO
The output of the above query is below. Check out the output compared to the sp_helpdb output above.
SQL Server SCHEMA_NAME and SCHEMA_ID Functions
SCHEMA_NAME and SCHEMA_ID are used to get the name and ID for the specified schema name or schema ID. If you query sys.schemas you get the schema name and schema_id.
Here is an example.
SELECT SCHEMA_NAME() AS [Schema Name], SCHEMA_NAME(1) AS [Schema Name using ID], SCHEMA_NAME(2) AS [Schema Name using ID], SCHEMA_NAME(3) AS [Schema Name using ID]
The output is the name of each schema we specified using its ID above. For the first item, the default is 1 that is why the first two columns are the same.
Similarly, the above schema names were copied from the result and passed to the function SCHEMA_ID to get their IDs using the below query.
SELECT SCHEMA_ID() AS [Schema ID], SCHEMA_ID('dbo') AS [Schema ID using Name], SCHEMA_ID('guest') AS [Schema ID using Name], SCHEMA_ID('INFORMATION_SCHEMA') AS [Schema ID using Name]
Below each statement has returned its schema IDs.
SQL Server OBJECT_NAME and OBJECT_ID Functions
This section will show how to get the object name associated with an object ID and vice versa using OBJECT_NAME and OBJECT_ID.
An object name must be specified in the OBJECT_ID function to get the ID of that object, and the ID of an object must be specified to get the name of that object using OBJECT_NAME.
The example below specifies table OrderDetails from database TESTDB to get the ID of this table. Also, we do the opposite using the object_id to get the name.
USE TESTDB SELECT OBJECT_ID('TESTDB.dbo.OrderDetails') AS [ObjectID] GO SELECT OBJECT_NAME(901578250) AS [ObjectName]
You can see the result in the example below.
SQL Server STATS_DATE Function
STATS_DATE will return the date of the most recent update for statistics on a table or indexed view by passing the object_id and stats_id.
SELECT object_id, name, stats_id FROM sys.stats WHERE object_id = 901578250GO SELECT STATS_DATE(901578250, 2) AS [Last stats update date]
The output of the above query returns a list of all stats names and ids for this object and the function shows when the stats were updated on stats ID 2.
You can also get the latest stats update date for all stats by executing the below query.
USE TESTDB SELECT name AS StatsName, STATS_DATE(object_id, stats_id) AS [Latest stats update date] FROM sys.stats WHERE object_id = OBJECT_ID('dbo.OrderDetails'); GO
Here, we can see all stats and their respective stats update date.
About the author
View all my tips
Article Last Updated: 2022-08-19