Metadata Functions in SQL Server and Their Use Cases

By:   |   Updated: 2022-08-19   |   Comments   |   Related: > Functions System


Problem

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.

Solution

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:

  • SERVERPROPERTY
  • DATABASEPROPERTYEX
  • DB_NAME and DB_ID
  • FILE_NAME, FILE_ID and FILE_IDEX
  • SCHEMA_NAME and SCHEMA_ID
  • OBJECT_NAME and OBJECT_ID
  • STATS_DATE

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.

SERVERPROPERTY

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.

DATABASEPROPERTYEX

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.

DB_NAME and DB_ID

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.

DB_NAME and DATABASEPROPERTYEX

SQL Server FILE_NAME, FILE_ID, and FILE_IDEX Functions

The following metadata functions are related to file-level information:

  • FILE_NAME
  • FILE_ID
  • FILEFILE_IDEX

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.

FILE_NAME

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.

FILE_ID and FILE_IDEX

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.

SCHEMA_NAME

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.

SCHEMA_ID

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.

OBJECT_NAME and OBJECT_ID

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.

STATS_DATE

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.

STASName and STATS_DATE
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-08-19

Comments For This Article

















get free sql tips
agree to terms