SQL Server Data Dictionary & SQL Catalog¶

-- name: by_schema
-- dialect: mssql
WITH pk_cols AS (
    SELECT DISTINCT
        idx_col.object_id,
        idx_col.column_id
    FROM sys.indexes AS idx
    INNER JOIN sys.index_columns AS idx_col
      ON idx.object_id = idx_col.object_id
     AND idx.index_id = idx_col.index_id
    WHERE idx.is_primary_key = 1
),
unique_cols AS (
    SELECT DISTINCT
        idx_col.object_id,
        idx_col.column_id
    FROM sys.indexes AS idx
    INNER JOIN sys.index_columns AS idx_col
      ON idx.object_id = idx_col.object_id
     AND idx.index_id = idx_col.index_id
    WHERE idx.is_unique = 1
)
SELECT
    s.name AS schema_name,
    tab.name AS table_name,
    c.column_id AS ordinal_position,
    c.name AS column_name,
    typ.name AS data_type,
    CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS is_nullable,
    OBJECT_DEFINITION(c.default_object_id) AS column_default,
    c.max_length,
    c.precision AS numeric_precision,
    c.scale AS numeric_scale,
    CAST(CASE WHEN pk_cols.column_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_primary,
    CAST(CASE WHEN unique_cols.column_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_unique,
    c.collation_name,
    CAST(c.is_sparse AS BIT) AS is_sparse,
    CAST(c.is_column_set AS BIT) AS is_column_set,
    CAST(c.is_hidden AS BIT) AS is_hidden,
    CAST(c.is_masked AS BIT) AS is_masked,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    CAST(CASE WHEN cc.object_id IS NOT NULL OR c.generated_always_type <> 0 THEN 1 ELSE 0 END AS BIT) AS is_generated,
    cc.definition AS generation_expression,
    CAST(cc.is_persisted AS BIT) AS is_persisted_computed,
    CAST(CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_identity,
    CASE WHEN ic.object_id IS NOT NULL THEN 'ALWAYS' ELSE NULL END AS identity_generation,
    CONVERT(BIGINT, ic.seed_value) AS identity_seed,
    CONVERT(BIGINT, ic.increment_value) AS identity_increment
FROM sys.columns AS c
INNER JOIN sys.types AS typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.tables AS tab ON c.object_id = tab.object_id
INNER JOIN sys.schemas AS s ON tab.schema_id = s.schema_id
LEFT JOIN sys.computed_columns AS cc ON c.object_id = cc.object_id AND c.column_id = cc.column_id
LEFT JOIN sys.identity_columns AS ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN pk_cols ON pk_cols.object_id = tab.object_id AND pk_cols.column_id = c.column_id
LEFT JOIN unique_cols ON unique_cols.object_id = tab.object_id AND unique_cols.column_id = c.column_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
ORDER BY s.name, tab.name, c.column_id;

-- name: by_table
-- dialect: mssql
WITH pk_cols AS (
    SELECT DISTINCT
        idx_col.object_id,
        idx_col.column_id
    FROM sys.indexes AS idx
    INNER JOIN sys.index_columns AS idx_col
      ON idx.object_id = idx_col.object_id
     AND idx.index_id = idx_col.index_id
    WHERE idx.is_primary_key = 1
),
unique_cols AS (
    SELECT DISTINCT
        idx_col.object_id,
        idx_col.column_id
    FROM sys.indexes AS idx
    INNER JOIN sys.index_columns AS idx_col
      ON idx.object_id = idx_col.object_id
     AND idx.index_id = idx_col.index_id
    WHERE idx.is_unique = 1
)
SELECT
    s.name AS schema_name,
    tab.name AS table_name,
    c.column_id AS ordinal_position,
    c.name AS column_name,
    typ.name AS data_type,
    CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS is_nullable,
    OBJECT_DEFINITION(c.default_object_id) AS column_default,
    c.max_length,
    c.precision AS numeric_precision,
    c.scale AS numeric_scale,
    CAST(CASE WHEN pk_cols.column_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_primary,
    CAST(CASE WHEN unique_cols.column_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_unique,
    c.collation_name,
    CAST(c.is_sparse AS BIT) AS is_sparse,
    CAST(c.is_column_set AS BIT) AS is_column_set,
    CAST(c.is_hidden AS BIT) AS is_hidden,
    CAST(c.is_masked AS BIT) AS is_masked,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    CAST(CASE WHEN cc.object_id IS NOT NULL OR c.generated_always_type <> 0 THEN 1 ELSE 0 END AS BIT) AS is_generated,
    cc.definition AS generation_expression,
    CAST(cc.is_persisted AS BIT) AS is_persisted_computed,
    CAST(CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_identity,
    CASE WHEN ic.object_id IS NOT NULL THEN 'ALWAYS' ELSE NULL END AS identity_generation,
    CONVERT(BIGINT, ic.seed_value) AS identity_seed,
    CONVERT(BIGINT, ic.increment_value) AS identity_increment
FROM sys.columns AS c
INNER JOIN sys.types AS typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.tables AS tab ON c.object_id = tab.object_id
INNER JOIN sys.schemas AS s ON tab.schema_id = s.schema_id
LEFT JOIN sys.computed_columns AS cc ON c.object_id = cc.object_id AND c.column_id = cc.column_id
LEFT JOIN sys.identity_columns AS ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN pk_cols ON pk_cols.object_id = tab.object_id AND pk_cols.column_id = c.column_id
LEFT JOIN unique_cols ON unique_cols.object_id = tab.object_id AND unique_cols.column_id = c.column_id
WHERE s.name = COALESCE(:schema_name, SCHEMA_NAME())
  AND tab.name = :table_name
ORDER BY c.column_id;

-- name: by_schema
-- dialect: mssql
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    kc.name AS constraint_name,
    kc.type_desc AS constraint_type,
    CAST(kc.is_system_named AS BIT) AS is_system_named,
    STUFF((
        SELECT ',' + c2.name
        FROM sys.index_columns AS ic2
        INNER JOIN sys.columns AS c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
        WHERE ic2.object_id = kc.parent_object_id
          AND ic2.index_id = kc.unique_index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS column_name,
    NULL AS definition
FROM sys.key_constraints AS kc
INNER JOIN sys.tables AS t ON kc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
UNION ALL
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    cc.name AS constraint_name,
    'CHECK_CONSTRAINT' AS constraint_type,
    CAST(cc.is_system_named AS BIT) AS is_system_named,
    NULL AS column_name,
    cc.definition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
UNION ALL
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    dc.name AS constraint_name,
    'DEFAULT_CONSTRAINT' AS constraint_type,
    CAST(dc.is_system_named AS BIT) AS is_system_named,
    c.name AS column_name,
    dc.definition
FROM sys.default_constraints AS dc
INNER JOIN sys.tables AS t ON dc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
ORDER BY schema_name, table_name, constraint_name;

-- name: by_table
-- dialect: mssql
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    kc.name AS constraint_name,
    kc.type_desc AS constraint_type,
    CAST(kc.is_system_named AS BIT) AS is_system_named,
    STUFF((
        SELECT ',' + c2.name
        FROM sys.index_columns AS ic2
        INNER JOIN sys.columns AS c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
        WHERE ic2.object_id = kc.parent_object_id
          AND ic2.index_id = kc.unique_index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS column_name,
    NULL AS definition
FROM sys.key_constraints AS kc
INNER JOIN sys.tables AS t ON kc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE s.name = :schema_name
  AND t.name = :table_name
UNION ALL
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    cc.name AS constraint_name,
    'CHECK_CONSTRAINT' AS constraint_type,
    CAST(cc.is_system_named AS BIT) AS is_system_named,
    NULL AS column_name,
    cc.definition
FROM sys.check_constraints AS cc
INNER JOIN sys.tables AS t ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE s.name = :schema_name
  AND t.name = :table_name
UNION ALL
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    dc.name AS constraint_name,
    'DEFAULT_CONSTRAINT' AS constraint_type,
    CAST(dc.is_system_named AS BIT) AS is_system_named,
    c.name AS column_name,
    dc.definition
FROM sys.default_constraints AS dc
INNER JOIN sys.tables AS t ON dc.parent_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE s.name = :schema_name
  AND t.name = :table_name
ORDER BY schema_name, table_name, constraint_name;

-- name: index_inputs_by_table
-- dialect: mssql
/* sqlspec:mssql:ddl_index_inputs */
WITH index_key_columns AS (
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(CAST(c.name AS NVARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns
    FROM sys.index_columns AS ic
    INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.is_included_column = 0
    GROUP BY ic.object_id, ic.index_id
),
index_included_columns AS (
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(CAST(c.name AS NVARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY ic.index_column_id) AS included_columns
    FROM sys.index_columns AS ic
    INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.is_included_column = 1
    GROUP BY ic.object_id, ic.index_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    CAST(i.is_unique AS BIT) AS is_unique,
    CAST(i.has_filter AS BIT) AS has_filter,
    i.filter_definition,
    kc.columns AS columns,
    inc.included_columns AS included_columns
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN index_key_columns AS kc ON kc.object_id = i.object_id AND kc.index_id = i.index_id
LEFT JOIN index_included_columns AS inc ON inc.object_id = i.object_id AND inc.index_id = i.index_id
WHERE s.name = :schema_name
  AND t.name = :table_name
  AND i.name IS NOT NULL
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
ORDER BY i.name;

-- name: table_inputs_by_table
-- dialect: mssql
/* sqlspec:mssql:ddl_table_inputs */
WITH primary_key_columns AS (
    SELECT
        pk_col.object_id,
        pk_col.column_id,
        pk.name AS primary_key_name,
        pk_col.key_ordinal AS primary_key_ordinal
    FROM sys.indexes AS pk
    INNER JOIN sys.index_columns AS pk_col
        ON pk.object_id = pk_col.object_id
       AND pk.index_id = pk_col.index_id
    WHERE pk.is_primary_key = 1
      AND pk_col.is_included_column = 0
)
SELECT
    s.name AS schema_name,
    tab.name AS table_name,
    c.name AS column_name,
    typ.name AS data_type,
    c.max_length,
    c.precision AS numeric_precision,
    c.scale AS numeric_scale,
    CAST(c.is_nullable AS BIT) AS is_nullable,
    c.column_id AS ordinal_position,
    OBJECT_DEFINITION(c.default_object_id) AS column_default,
    CAST(CASE WHEN cc.object_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_computed,
    cc.definition AS computed_definition,
    CAST(CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS BIT) AS is_identity,
    CONVERT(BIGINT, ic.seed_value) AS identity_seed,
    CONVERT(BIGINT, ic.increment_value) AS identity_increment,
    pk.primary_key_name AS primary_key_name,
    pk.primary_key_ordinal AS primary_key_ordinal
FROM sys.columns AS c
INNER JOIN sys.types AS typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.tables AS tab ON c.object_id = tab.object_id
INNER JOIN sys.schemas AS s ON tab.schema_id = s.schema_id
LEFT JOIN sys.computed_columns AS cc ON c.object_id = cc.object_id AND c.column_id = cc.column_id
LEFT JOIN sys.identity_columns AS ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN primary_key_columns AS pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
WHERE s.name = :schema_name
  AND tab.name = :table_name
ORDER BY c.column_id;

-- name: by_schema
-- dialect: mssql
SELECT
    OBJECT_SCHEMA_NAME(dep.referencing_id) AS referencing_schema_name,
    OBJECT_NAME(dep.referencing_id) AS referencing_object_name,
    dep.referencing_class_desc,
    dep.referenced_server_name,
    dep.referenced_database_name,
    dep.referenced_schema_name,
    dep.referenced_entity_name,
    dep.referenced_class_desc,
    CAST(dep.is_schema_bound_reference AS BIT) AS is_schema_bound_reference,
    CAST(dep.is_caller_dependent AS BIT) AS is_caller_dependent,
    CAST(dep.is_ambiguous AS BIT) AS is_ambiguous
FROM sys.sql_expression_dependencies AS dep
WHERE (:schema_name IS NULL OR OBJECT_SCHEMA_NAME(dep.referencing_id) = :schema_name)
ORDER BY OBJECT_SCHEMA_NAME(dep.referencing_id), OBJECT_NAME(dep.referencing_id), dep.referenced_entity_name;

-- name: by_schema
-- dialect: mssql
SELECT
    s.name AS schema_name,
    o.name AS object_name,
    o.type_desc AS object_type,
    c.name AS column_name,
    ep.name AS property_name,
    CONVERT(NVARCHAR(MAX), ep.value) AS property_value,
    ep.class_desc
FROM sys.extended_properties AS ep
LEFT JOIN sys.objects AS o ON ep.major_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
ORDER BY s.name, o.name, c.name, ep.name;

-- name: by_table
-- dialect: mssql
SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS table_schema,
    fk.name AS constraint_name,
    OBJECT_NAME(fk.parent_object_id) AS table_name,
    cpa.name AS column_name,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS referenced_schema,
    OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
    cref.name AS referenced_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns cpa
    ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
INNER JOIN sys.columns cref
    ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
WHERE OBJECT_SCHEMA_NAME(fk.parent_object_id) = :schema_name
  AND OBJECT_NAME(fk.parent_object_id) = :table_name
ORDER BY fk.name, fkc.constraint_column_id;

-- name: by_schema
-- dialect: mssql
SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS table_schema,
    fk.name AS constraint_name,
    OBJECT_NAME(fk.parent_object_id) AS table_name,
    cpa.name AS column_name,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS referenced_schema,
    OBJECT_NAME(fk.referenced_object_id) AS referenced_table,
    cref.name AS referenced_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns cpa
    ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
INNER JOIN sys.columns cref
    ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
WHERE (:schema_name IS NULL OR OBJECT_SCHEMA_NAME(fk.parent_object_id) = :schema_name)
ORDER BY OBJECT_SCHEMA_NAME(fk.parent_object_id), OBJECT_NAME(fk.parent_object_id), fk.name, fkc.constraint_column_id;

-- name: by_schema
-- dialect: mssql
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    CAST(i.is_unique AS BIT) AS is_unique,
    CAST(i.is_primary_key AS BIT) AS is_primary,
    CAST(i.is_unique_constraint AS BIT) AS is_unique_constraint,
    CAST(i.has_filter AS BIT) AS has_filter,
    i.filter_definition,
    ds.name AS data_space_name,
    ps.name AS partition_scheme_name,
    STUFF((
        SELECT ',' + c2.name
        FROM sys.index_columns AS ic2
        INNER JOIN sys.columns AS c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
        WHERE ic2.object_id = i.object_id
          AND ic2.index_id = i.index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS columns,
    STUFF((
        SELECT ',' + c3.name
        FROM sys.index_columns AS ic3
        INNER JOIN sys.columns AS c3 ON ic3.object_id = c3.object_id AND ic3.column_id = c3.column_id
        WHERE ic3.object_id = i.object_id
          AND ic3.index_id = i.index_id
          AND ic3.is_included_column = 1
        ORDER BY ic3.index_column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS included_columns
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
WHERE (:schema_name IS NULL OR s.name = :schema_name)
  AND i.name IS NOT NULL
ORDER BY s.name, t.name, i.name;

-- name: by_table
-- dialect: mssql
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    CAST(i.is_unique AS BIT) AS is_unique,
    CAST(i.is_primary_key AS BIT) AS is_primary,
    CAST(i.is_unique_constraint AS BIT) AS is_unique_constraint,
    CAST(i.has_filter AS BIT) AS has_filter,
    i.filter_definition,
    ds.name AS data_space_name,
    ps.name AS partition_scheme_name,
    STUFF((
        SELECT ',' + c2.name
        FROM sys.index_columns AS ic2
        INNER JOIN sys.columns AS c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
        WHERE ic2.object_id = i.object_id
          AND ic2.index_id = i.index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS columns,
    STUFF((
        SELECT ',' + c3.name
        FROM sys.index_columns AS ic3
        INNER JOIN sys.columns AS c3 ON ic3.object_id = c3.object_id AND ic3.column_id = c3.column_id
        WHERE ic3.object_id = i.object_id
          AND ic3.index_id = i.index_id
          AND ic3.is_included_column = 1
        ORDER BY ic3.index_column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS included_columns
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
WHERE s.name = :schema_name
  AND t.name = :table_name
  AND i.name IS NOT NULL
ORDER BY i.name;