|
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 |
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 ORDER BY tbl.[name], col.[name]; |
Login