SQL Language Reference

Overview

Metadata functions allow you to query information that the database management system stores about its own structure. This includes details about tables, columns, indexes, constraints, stored procedures, and more.

Utilizing metadata functions can greatly enhance the flexibility and maintainability of your SQL code by enabling it to adapt to changes in the database schema without requiring manual updates.

Common Metadata Functions

The following are some of the most commonly used metadata functions:

• OBJECT_ID ( object_name [ , object_type ] )

  Returns the database object ID.
  This function takes an object name (e.g., 'MyTable', 'usp_GetCustomers') and optionally an object type code (e.g., 'U' for user table, 'P' for stored procedure) and returns the unique ID assigned to that object within the database.
  
  Example:

  SELECT OBJECT_ID('Sales.Customers', 'U');

• COL_LENGTH ( table_name , column_name )

  Returns the length of a column.
  Given a table name and a column name, this function returns the defined length of that column in bytes. This is particularly useful for character-based data types.
  
  Example:

  SELECT COL_LENGTH('Person.Person', 'FirstName');

• COL_NAME ( table_id , column_id )

  Returns the name of a column.
  Using the table ID and the column ID, this function retrieves the name of the column. This is the inverse of what functions like COL_LENGTH might provide if you only have IDs.
  
  Example:

  SELECT COL_NAME(OBJECT_ID('Person.Person', 'U'), 2);

• COLUMNPROPERTY ( object_id , column_name , property )

  Returns the specified property of a column.
  This versatile function allows you to retrieve various properties of a column, such as whether it's an identity column, its precision, scale, or nullability. The 'property' argument can be one of several predefined strings (e.g., 'IsIdentity', 'Precision', 'Nullable').
  
  Example:

  SELECT COLUMNPROPERTY(OBJECT_ID('Production.Product', 'U'), 'ProductID', 'IsIdentity');

• TYPE_NAME ( type_id )

  Returns the name of a data type.
  Given the internal type ID of a data type, this function returns its human-readable name (e.g., 'int', 'varchar', 'datetime').
  
  Example:

  SELECT TYPE_NAME(39); -- Assuming 39 is the ID for INT

• SCHEMA_NAME ( schema_id )

  Returns the name of a schema.
  This function takes a schema ID and returns the corresponding schema name (e.g., 'dbo', 'Sales', 'HumanResources').
  
  Example:

  SELECT SCHEMA_NAME(4); -- Assuming 4 is the ID for the 'dbo' schema

• TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, etc. (from INFORMATION_SCHEMA views)

  Information schema views provide comprehensive metadata.
  While not strictly functions, the INFORMATION_SCHEMA views (e.g., INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.TABLES) are a standard SQL way to query database metadata. They offer a structured and portable way to access object information.
  
  Example:

  SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Customers';

Use Cases

• Dynamic SQL Generation: Create SQL statements programmatically based on the current database schema.
• Auditing and Logging: Track changes to database objects or retrieve information about who created or modified them.
• Data Profiling: Understand the characteristics of data within columns (e.g., maximum length, data types).
• Reporting and Documentation: Generate reports on database structure or assist in auto-generating documentation.
• Data Migration: Map columns and data types between different database systems or versions.

Important Considerations

• Permissions: Accessing metadata might require specific database permissions.
• Database Specificity: While some functions are standard SQL, others are specific to the database system (e.g., SQL Server, PostgreSQL, MySQL). Always refer to your specific database documentation.
• Performance: Extensive use of metadata queries, especially within loops or on large schemas, can impact performance. Optimize your queries where possible.