Cacher is the code snippet organizer for pro developers

SELECT      schema_name(t.schema_id)+'.'+t.name AS 'TableName'
            ,c.name  AS 'ColumnName'
			,TY.name as 'Type'
			,c.max_length
            ,schema_name(t.schema_id) as 'Schema'
            ,cons.CONSTRAINT_TYPE
            ,ref.TableNamePrimary
            ,ref.ColumnPrimary
 
FROM        sys.columns c
inner JOIN SYS.TYPES AS ty on C.system_type_id=TY.system_type_id
JOIN        sys.tables  t   ON c.object_id = t.object_id
outer apply (select TC.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
          ON --TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
             TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
             KU.COLUMN_NAME = c.name
            and ku.TABLE_NAME=t.name) AS cons
outer apply (
    SELECT
--    OBJECT_NAME(f.parent_object_id) TableName2,
--    COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName2,
   OBJECT_NAME (fc.referenced_object_id) TableNamePrimary,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ColumnPrimary
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t2
      ON t2.OBJECT_ID = fc.referenced_object_id
WHERE
   COL_NAME(fc.parent_object_id,fc.parent_column_id) =c.name
    and  OBJECT_NAME(f.parent_object_id)=t.name
 
 
    )ref
WHERE       c.name LIKE '%hs%' --AND c.name LIKE '%ConcatItemKey%'
ORDER BY    TableName
            ,ColumnName;