12. METADATA

Question

12. METADATA

Gurdeep Vaidya 0

Tell me all i need to know about metadata in MSSQL databases

Trevor Waldenburg 0

Metadata

What it is: Data about data - information about database structure, objects, relationships. Stored in system views accessible via sys.* or INFORMATION_SCHEMA.

sql

SELECT name, recovery_model_desc, state_desc FROM sys.databases

-- Columns with data types:
SELECT c.name AS column_name, t.name AS table_name,
       tp.name AS data_type, c.is_nullable, c.is_identity
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types tp ON c.user_type_id = tp.user_type_id
WHERE t.name = 'Customers'

-- Foreign keys:
SELECT fk.name, tp.name AS parent_table, tr.name AS referenced_table
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id

-- Indexes:
SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key
FROM sys.indexes i WHERE i.object_id = OBJECT_ID('Customers')

-- INFORMATION_SCHEMA (standard SQL, works on other DB systems too):
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers'

-- msdb - backup history:
SELECT database_name, backup_start_date, type, backup_size/1024/1024 AS MB
FROM msdb.dbo.backupset WHERE database_name = 'Northwind'
ORDER BY backup_finish_date DESC

-- msdb - SQL Agent Jobs:
SELECT name, enabled FROM msdb.dbo.sysjobs

1 answer

Your answer

Trevor Waldenburg 0 Reputation points

2026-05-16T02:02:02.1133333+00:00

Metadata

What it is: Data about data - information about database structure, objects, relationships. Stored in system views accessible via sys.* or INFORMATION_SCHEMA.

sql

SELECT name, recovery_model_desc, state_desc FROM sys.databases -- Columns with data types: SELECT c.name AS column_name, t.name AS table_name, tp.name AS data_type, c.is_nullable, c.is_identity FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types tp ON c.user_type_id = tp.user_type_id WHERE t.name = 'Customers' -- Foreign keys: SELECT fk.name, tp.name AS parent_table, tr.name AS referenced_table FROM sys.foreign_keys fk JOIN sys.tables tp ON fk.parent_object_id = tp.object_id JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id -- Indexes: SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key FROM sys.indexes i WHERE i.object_id = OBJECT_ID('Customers') -- INFORMATION_SCHEMA (standard SQL, works on other DB systems too): SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers' -- msdb - backup history: SELECT database_name, backup_start_date, type, backup_size/1024/1024 AS MB FROM msdb.dbo.backupset WHERE database_name = 'Northwind' ORDER BY backup_finish_date DESC -- msdb - SQL Agent Jobs: SELECT name, enabled FROM msdb.dbo.sysjobs

Answer 1

In SQL Server, metadata is “data about data”: information that describes databases, objects, result sets, security, and configuration. It is used for schema validation, code generation, ETL, diagnostics, and security.

Key areas of metadata in SQL Server:

Data model metadata (types and columns)

Describes data types and columns: identity, precision, nullability, length, etc.
Core Transact-SQL metadata functions:
- TYPE_ID(type_name) returns the ID of a data type.
- TYPE_NAME(type_id) returns the name of a data type from its ID.
- TYPEPROPERTY(type_name, 'PropertyName') returns properties such as Precision, Scale, AllowsNull, etc.
- COL_NAME(object_id, column_id) returns the column name for a given column ID.
- COL_LENGTH('schema.table', 'column') returns the length of a column in bytes.
- COLUMNPROPERTY(object_id, 'column_name', 'PropertyName') returns column properties such as IsIdentity, IsComputed, AllowsNull, and more.
Typical uses:
- Schema validation and compatibility checks.
- Dynamic SQL and code generation.
- ETL pipelines that adapt to schema changes.

Access path metadata (indexes and statistics)

Describes indexes and statistics used by the query optimizer.
Core functions:
- INDEX_COL('schema.table', index_id, key_ordinal) returns the name of an indexed column.
- INDEXKEY_PROPERTY(object_id, index_id, key_ordinal, 'PropertyName') returns index key properties (for example, sort order).
- INDEXPROPERTY(object_id, 'index_name', 'PropertyName') returns index properties such as whether an index is clustered, unique, or disabled.
- STATS_DATE(object_id, index_id) returns the last update date of statistics for a table or index.
Typical uses:
- Index and statistics health checks.
- Tuning and maintenance (deciding when to rebuild/reorganize indexes or update statistics).

Server, database, session, and principal metadata

Describes where and under which context code is running.
Core functions:
- SERVERPROPERTY('PropertyName') returns instance-level properties such as edition, collation, product level.
- DB_ID('database_name') and DB_NAME(database_id) map between database names and IDs.
- DATABASEPROPERTYEX('database_name', 'PropertyName') returns database-level properties (for example, collation, 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('principal_name') returns the ID of a database security principal.
- VERSION() returns the version string for Azure Synapse Analytics and Analytics Platform System (PDW).
Typical uses:
- Environment-aware scripts (different behavior per edition or database state).
- Diagnostics and logging (who ran what, from which app, on which database).

Object identification and programmability metadata

Describes schema-scoped objects (tables, views, procedures, functions, etc.) and their properties.
Core functions:
- OBJECT_ID('schema.object_name') returns the ID for a schema-scoped object.
- OBJECT_NAME(object_id) returns the object name from its ID.
- OBJECT_SCHEMA_NAME(object_id) returns the schema name of an object.
- SCHEMA_ID('schema_name') and SCHEMA_NAME(schema_id) map between schema names and IDs.
- PARSENAME('server.database.schema.object', part) returns a specific part of a multipart object name.
- @@PROCID returns the ID of the currently executing stored procedure.
- OBJECT_DEFINITION(object_id) returns the definition (source text) of programmable objects.
- OBJECTPROPERTY(object_id, 'PropertyName') and OBJECTPROPERTYEX(object_id, 'PropertyName') return object properties (for example, whether it is a table, view, function, or has specific behaviors).
Typical uses:
- Introspection and dynamic SQL.
- Dependency analysis and documentation.
- Code generation and deployment tooling.

Result set metadata (ODBC and generic applications)

Describes the columns of a query result: number of columns, data types, names, precision, nullability, etc.
In ODBC-based applications:
- SQLNumResultCols returns the number of columns in the result set.
- SQLDescribeCol returns basic column metadata (name, data type, length, etc.).
- SQLColAttribute returns additional attributes (precision, scale, nullability, etc.).
Important considerations:
- Result set metadata can differ from catalog metadata. For example, joins or expressions may change data types or updatability.
- Interoperable and generic applications must discover metadata at runtime rather than assume catalog definitions.

Sensitivity classification metadata

Describes information types and sensitivity labels for columns.

On SQL Server 2019 and later:

sys.sensitivity_classifications returns classified columns and their information types, labels, and ranks.

Example query:

    SELECT 
        schema_name(O.schema_id) AS schema_name,
        O.name AS table_name,
        C.name AS column_name,
        information_type, label, rank, rank_desc
    FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
        ON sc.major_id = O.object_id
    JOIN sys.columns C 
        ON sc.major_id = C.object_id
       AND sc.minor_id = C.column_id;

On SQL Server 2017 and earlier:
- Classification metadata is stored in extended properties (sys_information_type_name, sys_sensitivity_label_name) and accessed via sys.extended_properties.
Permissions:
- Viewing classification requires VIEW ANY SENSITIVITY CLASSIFICATION.
- Managing classification requires ALTER ANY SENSITIVITY CLASSIFICATION (implied by ALTER on the database or CONTROL SERVER).

Metadata visibility and security

Metadata visibility is controlled by permissions and affects:
- Catalog views.
- Metadata-exposing built-in functions.
- Compatibility views.
- Database Engine sp_help procedures.
- Information schema views.
- Extended properties.
Limited metadata accessibility means:
- System views may return only a subset of rows or an empty result set.
- Metadata functions such as OBJECTPROPERTYEX may return NULL.
- sp_help procedures may return partial results or NULL.
SQL modules (stored procedures, triggers) run under the caller’s security context and inherit these limitations.
To allow callers to view metadata, grant:
- VIEW DEFINITION, or in SQL Server 2022 and later, VIEW SECURITY DEFINITION or VIEW PERFORMANCE DEFINITION at object, database, or server scope.

Metadata in adapters and tools

BizTalk SQL adapter:
- Allows browsing of database tables, views, stored procedures, and functions.
- Surfaces operations such as Insert, Update, Select, Delete for tables and views.
- Exposes special operations like Set<column name> for large data types (varchar(max), nvarchar(max), varbinary(max)), and generic operations (ExecuteNonQuery, ExecuteReader, ExecuteScalar).
- Supports Polling and Notification operations for inbound messages.
These capabilities rely on SQL Server metadata to discover objects and operations.

Metadata for corruption and consistency

Some engine errors (for example, error 606) indicate metadata inconsistency.
When such errors occur, commands like DBCC CHECKDB or DBCC CHECKCATALOG are used to diagnose and confirm corruption.
If corruption is confirmed, the safest resolution is typically to restore from a clean backup.

Practical uses of metadata

Schema discovery and documentation.
Dynamic ETL and data integration.
Performance tuning (index and statistics metadata).
Security auditing and classification.
Application-level introspection (generic query tools, ORMs, adapters).

References:

Share via

12. METADATA

Metadata

1 answer

Your answer