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
|
SELECT c.table_name AS tableName, c.table_schema AS schemaName, c.column_name, c.data_type, FK.table_ref
, CAST(CASE WHEN t.type = 'U' THEN 'U' ELSE 'V' END AS VARCHAR) AS is_table_or_view
, CAST(CASE WHEN c.is_nullable = 'YES' THEN 1 ELSE 0 END AS BIT) AS is_nullable
, CAST(CASE WHEN COLUMNPROPERTY(object_id(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), c.column_name, 'IsIdentity') = 1 THEN 1 ELSE 0 END AS BIT) AS is_identity
, ISNULL(TKEY.is_primaryKey, 0) AS is_primaryKey
, ISNULL(TKEY.is_foreignKey, 0) AS is_foreignKey
, c.character_maximum_length
, c.numeric_precision
, c.numeric_scale
FROM information_schema.columns c
INNER JOIN sys.objects t ON t.object_id = object_id(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)
OUTER APPLY (
SELECT CAST(OBJECTPROPERTY(OBJECT_ID(cu.constraint_schema + '.' + QUOTENAME(cu.constraint_name)), 'IsPrimaryKey') AS BIT)
, CAST(OBJECTPROPERTY(OBJECT_ID(cu.constraint_schema + '.' + QUOTENAME(cu.constraint_name)), 'IsForeignKey') AS BIT)
FROM information_schema.key_column_usage cu
WHERE cu.table_name = c.table_name AND cu.table_schema = c.table_schema
AND cu.column_name = c.column_name
) AS TKEY (is_primaryKey, is_foreignKey)
OUTER APPLY (
SELECT object_name(fk.referenced_object_id)
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id AND TKEY.is_foreignKey = 1
INNER JOIN sys.columns cl ON cl.column_id = fkc.parent_column_id AND cl.object_id = fkc.parent_object_id
WHERE cl.object_id = object_id(c.TABLE_NAME)
AND cl.name = c.COLUMN_NAME
) AS FK (table_ref) |
Partager