DECLARE @FlatTable TABLE
(
TableName NVARCHAR(128),
ColumnName NVARCHAR(128),
DataType NVARCHAR(128),
[MaxLength] INT,
[Precision] INT,
Scale INT,
Nullable BIT,
[Identity] BIT,
PrimaryKey BIT,
ForeignKeyName NVARCHAR(128),
ParentTable NVARCHAR(128),
ParentColumn NVARCHAR(128),
ReferencedTable NVARCHAR(128),
ReferencedColumn NVARCHAR(128)
);
INSERT INTO @FlatTable
SELECT tbl.[name] AS [TableName],
col.[name] AS [ColumnName],
ty.[name] AS [DataType],
col.[max_length] AS [MaxLength],
col.[precision] AS [Precision],
col.[scale] AS [Scale],
col.[is_nullable] AS [Nullable],
col.[is_identity] AS [Identity],
CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
fk.name AS ForeignKeyName,
OBJECT_NAME(fk.parent_object_id) AS ParentTable,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM sys.tables tbl
INNER JOIN sys.columns col
ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
FROM sys.index_columns ind
INNER JOIN sys.key_constraints pks
ON pks.[parent_object_id] = ind.[object_id]
AND pks.[unique_index_id] = ind.[index_id]
WHERE pks.[type] = 'PK'
) pkcol
ON pkcol.[table_object_id] = tbl.[object_id]
AND pkcol.[column_id] = col.[column_id]
LEFT JOIN
sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN
sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
WHERE 1=1
-- tbl.name does not start with __
AND tbl.name != '__RefactorLog'
ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];
DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))
INSERT INTO @StuffedTable
SELECT
TableName,
STUFF
(
(
SELECT ',' +
CASE
WHEN PrimaryKey = 1 THEN '*'
WHEN ForeignKeyName IS NOT NULL THEN '+'
ELSE '' END +
ColumnName + ' ' + [DataType] +
CASE
WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'
WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'
ELSE '' END
FROM @FlatTable As T2
WHERE T2.TableName = T1.TableName
FOR XML PATH (''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, '') + ','
AS [Columns]
FROM @FlatTable As T1
GROUP BY TableName
DECLARE @ResultString NVARCHAR(MAX) =
(SELECT
STUFF
(
(
SELECT ',' +
'[' + TableName + ']' + ',' +
Columns
FROM @StuffedTable As T2
FOR XML PATH (''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, ''))
PRINT
'```kroki' + CHAR(13) + CHAR(10) +
'erd' + CHAR(13) + CHAR(10) +
REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))
+ '```' + CHAR(13) + CHAR(10)
Login