SQL Schema Assistant

SET NOCOUNT ON;

-- 1. User Tables (Columns, Primary Keys, Foreign Keys)
SELECT 
    '### ' + o.name + ' (TABLE)' + CHAR(13) + CHAR(10) +
    STRING_AGG(CAST('- ' + c.name + ' (' + ty.name + 
        CASE 
            WHEN pk.column_id IS NOT NULL THEN ', PK'
            WHEN fk.referenced_table IS NOT NULL THEN ', FK -> ' + fk.referenced_table + '.' + fk.referenced_column
            ELSE ''
        END + ')' AS VARCHAR(MAX)), CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY c.column_id)
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.index_columns ic
    JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_primary_key = 1
) pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
LEFT JOIN (
    SELECT 
        fkc.parent_object_id AS object_id,
        fkc.parent_column_id AS column_id,
        OBJECT_NAME(fkc.referenced_object_id) AS referenced_table,
        COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS referenced_column,
        ROW_NUMBER() OVER(PARTITION BY fkc.parent_object_id, fkc.parent_column_id ORDER BY fkc.constraint_object_id) AS rn
    FROM sys.foreign_key_columns fkc
) fk ON c.object_id = fk.object_id AND c.column_id = fk.column_id AND fk.rn = 1
WHERE o.type = 'U' 
  AND o.is_ms_shipped = 0
GROUP BY o.name

UNION ALL

-- 2. Views (Columns + Complete Underlying SQL Query)
SELECT 
    '### ' + v.name + ' (VIEW)' + CHAR(13) + CHAR(10) +
    'Columns:' + CHAR(13) + CHAR(10) +
    cols.column_list + CHAR(13) + CHAR(10) +
    'Underlying View SQL Query:' + CHAR(13) + CHAR(10) +
    '```sql' + CHAR(13) + CHAR(10) +
    LTRIM(RTRIM(m.definition)) + CHAR(13) + CHAR(10) +
    '```'
FROM sys.views v
JOIN sys.sql_modules m ON v.object_id = m.object_id
CROSS APPLY (
    SELECT STRING_AGG(CAST('- ' + c.name + ' (' + ty.name + ')' AS VARCHAR(MAX)), CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY c.column_id) AS column_list
    FROM sys.columns c
    JOIN sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE c.object_id = v.object_id
) cols
WHERE v.is_ms_shipped = 0
ORDER BY 1 ASC;