Select Column info and join with constraint info

Question

Hi! How can I join these 2 queries to get all table info in one result?

A - Get all columns and their datatype belonging to table Customers

select COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Customers'

B - Get all constraints on the table Customers

SELECT        KCU1.CONSTRAINT_NAME AS 'ConstraintName', KCU1.COLUMN_NAME AS 'ColumnName', KCU2.TABLE_NAME AS 'ForeignTableName', 
                             KCU2.COLUMN_NAME AS 'ForeignColumnName'
    FROM            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN
                             INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND 
                             KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN
                             INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND 
                             KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND 
                             KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION AND KCU1.TABLE_NAME = 'Customers'

The result I want is a list of all columns and those that have a foreign key connection to another table should be denoted as such. (Sorry for my poor terminology)

Answer 1

Ah! Got it ! Here it is:

SELECT        CL.COLUMN_NAME AS 'ColumnName', CL.DATA_TYPE AS 'DataType', CL.CHARACTER_MAXIMUM_LENGTH AS 'MaxLength', 
                         KCU1.CONSTRAINT_NAME AS 'ConstraintName', KCU2.TABLE_NAME AS 'ForeignTableName', KCU2.COLUMN_NAME AS 'ForeignColumnName'
FROM            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN
                         INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND 
                         KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN
                         INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND 
                         KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND 
                         KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION AND KCU1.TABLE_NAME = 'Customers' RIGHT OUTER JOIN
                         INFORMATION_SCHEMA.COLUMNS AS CL ON KCU1.TABLE_NAME = CL.TABLE_NAME AND KCU1.COLUMN_NAME = CL.COLUMN_NAME
WHERE        (CL.TABLE_NAME = N'Customers')
ORDER BY CL.ORDINAL_POSITION