Table Breakdown Analysis

Script: scripts/utilities/table_breakdown.sql

Overview

This comprehensive utility script provides detailed metadata analysis for a specific table. It retrieves extensive column information including data types, constraints, indexes, primary keys, foreign keys, and default values. This script is invaluable for database documentation, schema analysis, and understanding table structure.

Parameters

TABLE_NAME - The name of the table to analyze (currently hardcoded to 'BENEFITS')

Data Components

The script returns comprehensive table metadata including:

Basic Column Information - Name, data type, length, nullability
Identity and Constraints - Identity columns, default constraints
Key Relationships - Primary keys and foreign keys
Index Information - Associated indexes and their types

Output Format

Column	Data Type	Description
TableName	nvarchar	Name of the table being analyzed
ColumnName	nvarchar	Name of each column
DataType	nvarchar	SQL Server data type
MaxLength	smallint	Maximum length for character/binary types
IsNullable	bit	Whether column allows NULL values
IsIdentity	bit	Whether column is an identity column
ColumnID	int	Ordinal position of column
DefaultConstraintID	int	ID of default constraint (if any)
DefaultValue	nvarchar	Default value definition
PrimaryKey	nvarchar	Primary key constraint name
ForeignKey	nvarchar	Foreign key constraint name
IndexName	nvarchar	Associated index name
IndexType	nvarchar	Type of index (CLUSTERED, NONCLUSTERED, etc.)

Technical Implementation

The script uses:

sys.columns, sys.tables, sys.types system views for basic column information
sys.default_constraints for default value information
sys.index_columns and sys.indexes for index information
sys.key_constraints for primary key information
sys.foreign_keys for foreign key relationships
Multiple LEFT JOINs to combine all metadata into a single result set

Notes

Currently configured for 'BENEFITS' table - modify the WHERE clause for different tables
Provides comprehensive schema documentation in a single query
Useful for database documentation, migration planning, and schema analysis
May return multiple rows per column if the column participates in multiple indexes
Consider filtering or grouping results if analyzing tables with many indexes

T-SQL

table_breakdown.sql

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    c.column_id AS ColumnID,
    c.default_object_id AS DefaultConstraintID,
    dc.definition AS DefaultValue,
    pk.name AS PrimaryKey,
    fk.name AS ForeignKey,
    i.name AS IndexName,
    i.type_desc AS IndexType
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT JOIN sys.key_constraints pk ON pk.parent_object_id = t.object_id AND pk.type = 'PK'
LEFT JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id
WHERE t.name = 'BENEFITS' -- Replace with your table name
ORDER BY c.column_id;

Content Inventory

Doc File: content/docs/utilities/table_breakdown.mdx
SQL Script: SQL/utilities/table_breakdown.sql

Table Breakdown Analysis

On this page