I needed to write a query where I can get info about all columns (with data type) and also to know which ones are PK/FK. For FK, needed the additional info like which other table is it from. I have got the query which works, but it looks a bit overkill.
Can this be done better way? I don't like the sub-query joins in it. It has to be a query, can't be done via SP.
My example is against Northwind (with some additional FK relationships that I was testing)
SELECT
t.name AS TableName,
t.object_id AS TableObjectId,
tCols.column_name AS ColumnName,
tCols.data_type AS ColumnDataType,
ISNULL(tCols.numeric_scale, 0) AS ColumnDecimalPlaces,
CASE tConstraints.CONSTRAINT_TYPE
WHEN 'PRIMARY KEY'
THEN '1'
ELSE '0'
END AS ISPK,
CASE tConstraints.CONSTRAINT_TYPE
WHEN 'FOREIGN KEY'
THEN '1'
ELSE '0'
END AS ISFK,
tConstraints.CONSTRAINT_TYPE,
tConstraints.CONSTRAINT_NAME,
fkInfo.FK_name,
fkInfo.PK_column,
fkInfo.PK_table,
fkInfo.PK_name
FROM sys.objects t
LEFT JOIN information_schema.columns tCols ON tCols.TABLE_NAME = t.name
LEFT JOIN (
SELECT
tc.CONSTRAINT_NAME,
tc.TABLE_NAME,
tc.CONSTRAINT_TYPE,
kcu.COLUMN_NAME
FROM information_schema.table_constraints tc
INNER JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
) AS tConstraints
ON t.name = tConstraints.TABLE_NAME
AND tCols.column_name = tConstraints.COLUMN_NAME
LEFT JOIN (
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
) AS fkInfo ON t.name = fkInfo.FK_table
AND tCols.column_name = fkInfo.FK_column
WHERE t.name = 'Products'
ORDER BY 3
