https://kroki.io/examples.html#erd
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
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