SQL Server Table Metadata

sql_table_metadata.sql

DECLARE @SchemaName sysname = ''
      , @TableName sysname = '';

WITH pk /* Primary Keys */
AS ( SELECT   t.object_id
            , pkc.column_id
            , ic.index_column_id pk_index
            , pk.name            AS pk_name
     FROM     sys.tables        t
         JOIN sys.schemas       s ON s.schema_id            = t.schema_id
         JOIN sys.indexes       pk ON t.object_id           = pk.object_id
                                      AND pk.is_primary_key = 1
         JOIN sys.index_columns ic ON ic.object_id          = pk.object_id
                                      AND ic.index_id       = pk.index_id
         JOIN sys.columns       pkc ON pk.object_id         = pkc.object_id
                                       AND pkc.column_id    = ic.column_id
     WHERE    s.name     = @SchemaName
              AND t.name = @TableName )
   , ak /* Alternate Keys */
AS ( SELECT   t.object_id
            , t.name
            , akc.column_id
            , ic.index_column_id ak_index
            , ak.name            AS ak_name
     FROM     sys.tables        t
         JOIN sys.schemas       s ON s.schema_id                  = t.schema_id
         JOIN sys.indexes       ak ON t.object_id                 = ak.object_id
                                      AND ak.is_primary_key       = 0
                                      AND ak.is_unique_constraint = 1
         JOIN sys.index_columns ic ON ic.object_id                = ak.object_id
                                      AND ic.index_id             = ak.index_id
         JOIN sys.columns       akc ON ak.object_id               = akc.object_id
                                       AND akc.column_id          = ic.column_id
     WHERE    s.name     = @SchemaName
              AND t.name = @TableName )
   , fk /* foreign keys */
AS ( SELECT   t.object_id
            , c.column_id
            , 1       AS is_foreign_key
            , fk.name AS fk_name
     FROM     sys.tables              t
         JOIN sys.schemas             s ON s.schema_id                    = t.schema_id
         JOIN sys.columns             c ON c.object_id                    = t.object_id
         JOIN sys.foreign_keys        fk ON fk.parent_object_id           = t.object_id
         JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id         = t.object_id
                                             AND fkc.constraint_object_id = fk.object_id
                                             AND fkc.parent_object_id     = fk.parent_object_id
                                             AND c.column_id              = fkc.parent_column_id
     WHERE    s.name     = @SchemaName
              AND t.name = @TableName )
   , dc /* default constraints */
AS ( SELECT   t.object_id
            , c.column_id
            , d.name            AS dc_name
            , d.definition      AS dc_definition
            , d.is_system_named AS dc_is_system_named
     FROM     sys.tables              t
         JOIN sys.schemas             s ON s.schema_id            = t.schema_id
         JOIN sys.columns             c ON c.object_id            = t.object_id
         JOIN sys.default_constraints d ON d.parent_object_id     = t.object_id
                                           AND d.parent_column_id = c.column_id
     WHERE    s.name     = @SchemaName
              AND t.name = @TableName )
SELECT        t.object_id
            , s.name
            , t.name
            , c.name
            , c.column_id
            , y.name
            , y.max_length
            , y.precision
            , y.scale
            , c.is_nullable
            , c.is_identity
            , c.is_computed
            , pk.pk_name
            , pk.pk_index
            , ak.ak_name
            , ak.ak_index
            , fk.is_foreign_key
            , fk.fk_name
            , dc.dc_name
            , dc.dc_definition
            , dc.dc_is_system_named
FROM          sys.tables  t
    JOIN      sys.schemas s ON s.schema_id    = t.schema_id
    JOIN      sys.columns c ON c.object_id    = t.object_id
    JOIN      sys.types   y ON y.user_type_id = c.user_type_id
    LEFT JOIN pk ON pk.column_id              = c.column_id
                    AND pk.object_id          = c.object_id
                    AND pk.object_id          = t.object_id
    LEFT JOIN ak ON ak.column_id              = c.column_id
                    AND ak.object_id          = c.object_id
                    AND ak.object_id          = t.object_id
    LEFT JOIN fk ON fk.column_id              = c.column_id
                    AND fk.object_id          = c.object_id
                    AND fk.object_id          = t.object_id
    LEFT JOIN dc ON dc.object_id              = t.object_id
                    AND dc.object_id          = c.object_id
                    AND dc.column_id          = c.column_id
WHERE         s.name     = @SchemaName
              AND t.name = @TableName;