SQL Server Database Schema and Data Dictionary Queries

calendar Jun 18, 2026 / · 6 min read · sql server information_schema data dictionary database schema metadata sys.objects sys.columns sys.sql_modules catalog views documentation database structure ansi standard scripts  ·
Share on: linkedin copy

Documenting a database starts with a data dictionary: every table, every column, its data type, length, nullability, and default. SQL Server exposes this through the ANSI-standard INFORMATION_SCHEMA views, which produce a portable schema report that reads almost like English and runs unchanged on other ISO-compliant database engines.

Purpose and Overview

INFORMATION_SCHEMA is a set of views defined by the ISO SQL standard and implemented in a special schema present in every SQL Server database. Their purpose is insulation: they present an internal, system-table-independent view of metadata, so applications keep working even when the underlying system tables change between releases. That portability is the main reason to reach for them — a data-dictionary query written against INFORMATION_SCHEMA will run on SQL Server, PostgreSQL, MySQL, and other engines that implement the standard.

The workhorses for schema documentation are INFORMATION_SCHEMA.TABLES (one row per table or view, with a TABLE_TYPE of BASE TABLE or VIEW) and INFORMATION_SCHEMA.COLUMNS (one row per column, with ordinal position, data type, length, precision, scale, nullability, and default). Add INFORMATION_SCHEMA.KEY_COLUMN_USAGE and TABLE_CONSTRAINTS and you can annotate the dictionary with primary and foreign keys.

These ISO views are the standards-based counterpart to the proprietary sys catalog views, and the modern, supported alternative to reading the legacy SQL Server 2000 system tables directly. Each layer has its place; this post covers when the ANSI views are the right choice and where they fall short.

Code Breakdown

The core data-dictionary query — every column of every base table, in table and ordinal order:

 1SELECT
 2    c.TABLE_SCHEMA,
 3    c.TABLE_NAME,
 4    c.ORDINAL_POSITION,
 5    c.COLUMN_NAME,
 6    c.DATA_TYPE,
 7    c.CHARACTER_MAXIMUM_LENGTH,
 8    c.NUMERIC_PRECISION,
 9    c.NUMERIC_SCALE,
10    c.IS_NULLABLE,
11    c.COLUMN_DEFAULT
12FROM INFORMATION_SCHEMA.COLUMNS AS c
13INNER JOIN INFORMATION_SCHEMA.TABLES AS t
14    ON  c.TABLE_SCHEMA = t.TABLE_SCHEMA
15    AND c.TABLE_NAME   = t.TABLE_NAME
16WHERE t.TABLE_TYPE = 'BASE TABLE'
17ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION;

Filtering tables with INFORMATION_SCHEMA.TABLES

The join to INFORMATION_SCHEMA.TABLES exists to filter out views. Its TABLE_TYPE column returns BASE TABLE or VIEW; restricting to BASE TABLE keeps the dictionary to physical tables. Drop the predicate to document views as well, or change it to 'VIEW' to list only views.

Reading column definitions from INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.COLUMNS is the heart of the report. ORDINAL_POSITION preserves the column order as defined. DATA_TYPE gives the system type name, and the shape columns interpret by type: CHARACTER_MAXIMUM_LENGTH is populated for string and binary types (and returns -1 for varchar(max) and similar large types), while NUMERIC_PRECISION and NUMERIC_SCALE apply to numeric types. IS_NULLABLE returns YES or NO, and COLUMN_DEFAULT carries the default expression.

Adding keys with KEY_COLUMN_USAGE

To annotate which columns participate in keys, join INFORMATION_SCHEMA.KEY_COLUMN_USAGE to INFORMATION_SCHEMA.TABLE_CONSTRAINTS on the constraint name and filter TABLE_CONSTRAINTS.CONSTRAINT_TYPE to 'PRIMARY KEY' or 'FOREIGN KEY'. The ORDINAL_POSITION in KEY_COLUMN_USAGE orders the columns within each composite key, the same way key_ordinal does in the catalog views.

Rendering a readable type string

A raw dictionary splits the data type across DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE. For a human-facing report, collapse them into one column:

 1SELECT
 2    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
 3    DATA_TYPE +
 4        CASE
 5            WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)'
 6            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
 7                 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(11)) + ')'
 8            WHEN DATA_TYPE IN ('decimal','numeric')
 9                 THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR(11)) + ','
10                          + CAST(NUMERIC_SCALE AS VARCHAR(11)) + ')'
11            ELSE ''
12        END AS full_type,
13    IS_NULLABLE
14FROM INFORMATION_SCHEMA.COLUMNS
15ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

This produces values like varchar(50), nvarchar(max), and decimal(10,2) in a single column — the form developers actually read in DDL — turning the dictionary into documentation that needs no further interpretation.

Extending the dictionary to views and routines

A complete data dictionary covers more than tables. INFORMATION_SCHEMA.VIEWS exposes each view's VIEW_DEFINITION, and INFORMATION_SCHEMA.ROUTINES lists stored procedures and functions with their ROUTINE_TYPE, DATA_TYPE (the return type for functions), and ROUTINE_DEFINITION. Because both share the same TABLE_SCHEMA/TABLE_NAME naming convention as the column views, you can union or join them into the same report. One caveat: the definition columns are nvarchar(4000) and truncate longer object bodies, so for the full text of a long view or procedure, fall back to the OBJECT_DEFINITION() function or sys.sql_modules, which return the complete nvarchar(max) definition.

When to prefer the sys views

Microsoft's own documentation cautions that INFORMATION_SCHEMA views expose only a subset of metadata and should not be used to determine an object's schema reliably — sys.objects is the authoritative source. The ISO views also lag new features: they will not surface index details, computed-column definitions, temporal columns, or other SQL-Server-specific metadata. A concrete example is data type: INFORMATION_SCHEMA.COLUMNS reports the base type name but not whether a column is computed, an identity, or a sparse column — for those you need sys.columns and its is_computed, is_identity, and is_sparse flags. Use INFORMATION_SCHEMA for portable, column-level documentation; switch to the sys catalog views when you need depth or guaranteed accuracy.

Key Benefits and Use Cases

  • Portable documentation — the same query produces a data dictionary on any ISO-compliant database engine.
  • Readable output — column names like DATA_TYPE and IS_NULLABLE make the result self-describing for non-DBA stakeholders.
  • Schema comparison — capture the dictionary from two environments and diff it to find drift between dev, test, and production.
  • Generation source — the column metadata drives code generation, ORM mapping checks, and ETL column-mapping documents.
  • Onboarding aid — a generated data dictionary orients new developers far faster than reading DDL scripts.

Performance Considerations

  • Metadata-only, but joins fan out: column-by-table joins on large schemas return many rows; filter by TABLE_SCHEMA or TABLE_NAME when documenting a subset.
  • INFORMATION_SCHEMA is intentionally limited: it is incomplete by design and not updated for every new feature, so treat absence of a column as "not exposed here," not "does not exist."
  • Schema lookups are not authoritative: per Microsoft guidance, do not rely on INFORMATION_SCHEMA to resolve an object's true schema — use sys.objects for that.
  • Permission scoping applies: the views return only objects the current user has some permission on, so a restricted login produces a partial dictionary.

Practical Tips

  • Concatenate a readable type string with a CASE expression — for example, appending ( + length + ) for character types — to render varchar(50) rather than separate type and length columns.
  • Use STRING_AGG over the column list per table to produce a one-line column summary for a compact overview page.
  • Join in sys.extended_properties (a sys view) when you want column descriptions, since INFORMATION_SCHEMA does not expose MS_Description values.
  • Export the result to CSV or a wiki table on each release to keep living documentation in sync with the schema.
  • For primary-key-specific reporting, pair this dictionary with the focused sp_pkeys and primary key metadata approach.

Conclusion

INFORMATION_SCHEMA.COLUMNS, TABLES, and KEY_COLUMN_USAGE give you a portable, standards-based data dictionary in a single query — ideal for documentation, schema comparison, and onboarding. Their portability and readability are real advantages, balanced by their deliberate incompleteness. Reach for them when you want a clean, cross-platform schema report, and step down to the sys catalog views when you need the full, SQL-Server-specific picture.

References

Posts in this series