Metadata functions (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

This article describes scalar functions that return information about the database and database objects.

All metadata functions are nondeterministic. They don't always return the same results every time they're called, even with the same set of input values.

Server, database, session, and principal context metadata

Find out where you're running (instance and database) and who you're running as or through (client application, database principal). Use this information for environment-aware scripts and diagnostics.

Function	Description
SERVERPROPERTY	Returns properties of the SQL Server instance such as edition, collation, or product level.
DB_ID	Returns the ID of a database.
DB_NAME	Returns the name of a database given the ID.
DATABASEPROPERTYEX	Returns database-level property values, such as collation and status.
ORIGINAL_DB_NAME	Returns the original database name before a restore sequence.
APP_NAME	Returns the application name for the current session.
DATABASE_PRINCIPAL_ID	Returns the principal ID for a database security principal.
VERSION	Returns the version string for Azure Synapse Analytics and Analytics Platform System (PDW).

¹ Applies to: Azure Synapse Analytics and Analytics Platform System (PDW) only

Object identification and name resolution

Resolve IDs to names across objects and schemas, and parse multipart identifiers. These functions provide core support for introspection and dynamic SQL.

Function	Description
OBJECT_ID	Returns the ID for a schema-scoped object.
OBJECT_NAME	Returns the object name for an object ID.
OBJECT_SCHEMA_NAME	Returns the schema name of an object.
SCHEMA_ID	Returns a schema's ID.
SCHEMA_NAME	Returns a schema's name by ID.
PARSENAME	Returns a part of a multipart object name (server, database, schema, object).
@@PROCID	Returns the ID of the currently executing stored procedure.

Object capabilities and programmability metadata

Inspect what an object is, how it behaves, and how it's implemented. This inspection includes the definition and properties, and CLR assembly metadata.

Function	Description
OBJECT_DEFINITION	Returns the definition (source text) of programmable objects.
OBJECTPROPERTY	Returns a property for an object (for example, whether it's a view, table, and so on).
OBJECTPROPERTYEX	Returns extended object property information.
ASSEMBLYPROPERTY	Returns a specified property value of a SQL CLR assembly.

Data model metadata (types and columns)

Understand type identity and properties, and column identity and properties. Use this metadata for schema validation, code generation, ETL, and compatibility checks.

Function	Description
TYPE_ID	Returns the ID of a data type.
TYPE_NAME	Returns a data type name by ID.
TYPEPROPERTY	Returns a property of a data type, such as precision or nullable.
COL_NAME	Returns the column name for a given column ID.
COL_LENGTH	Returns the length of a column in bytes.
COLUMNPROPERTY	Returns a property of a column, such as whether it's an identity or computed column.

Access path metadata (indexes and statistics)

Inspect indexing structures and statistics freshness. Use this metadata in tuning workflows and metadata-driven maintenance.

Function	Description
INDEX_COL	Returns the name of an indexed column.
INDEXKEY_PROPERTY	Returns a property of an index key.
INDEXPROPERTY	Returns a property of an index, such as clustered or disabled.
STATS_DATE	Returns the date statistics were last updated for a table or index.

Physical storage and full-text metadata

Inspect the physical layout (files and filegroups) and full-text components. Use this information for storage management, troubleshooting, and configuration auditing.

Function	Description
FILE_ID	Returns the file ID.
FILE_IDEX	Returns the file ID based on file name.
FILE_NAME	Returns the file name for a file ID.
FILEGROUP_ID	Returns a filegroup's ID.
FILEGROUP_NAME	Returns the name of a filegroup.
FILEGROUPPROPERTY	Returns a property of a filegroup.
FILEPROPERTY	Returns a file property, such as size or status.
FULLTEXTCATALOGPROPERTY	Returns a property of a full-text catalog.
FULLTEXTSERVICEPROPERTY	Returns full-text service properties, like load status.

Runtime coordination and generated value metadata

Coordinate work across sessions by using application locks. Retrieve generated numeric values for identity and sequence objects.

Function	Description
APPLOCK_MODE	Returns the lock mode held by the current session.
APPLOCK_TEST	Tests whether a lock can be acquired without actually acquiring it.
SCOPE_IDENTITY	Returns the last identity value generated in the current scope.
NEXT VALUE FOR	Returns the next value in a sequence object.