How can I merge these 2 queries together?

Question

I've written a query to lookup column metadata based on a database (Tracker) and table (Work):

SELECT      Cols.Name,
            TYPE_NAME(Cols.user_type_id) Type,
            CAST(ISNULL(OBJECTPROPERTY(OBJECT_ID(Keys.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) AS BIT) PrimaryKey,
            Cols.is_identity [Identity],
            Cols.is_nullable Nullable,
            CAST(ColumnProperty(object_id, Cols.name, 'IsComputed') AS BIT) Computed,
            CASE WHEN Cons.CONSTRAINT_TYPE IS NULL THEN NULL ELSE Keys.CONSTRAINT_NAME END ForeignKey
FROM        sys.columns Cols

LEFT JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE Keys
ON          Keys.COLUMN_NAME = Cols.name
AND         Keys.TABLE_CATALOG = 'Tracker'
AND         Keys.TABLE_NAME = 'Work'

LEFT JOIN   INFORMATION_SCHEMA.TABLE_CONSTRAINTS Cons
ON          Cons.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
AND         Cons.CONSTRAINT_TYPE = 'FOREIGN KEY'

WHERE       Cols.object_id = OBJECT_ID('Work')
ORDER BY    Cols.column_id ASC

Which produces this result:

I've also written this query to lookup relationship info for a foreign key:

SELECT      OBJECT_NAME(FKCols.referenced_object_id) ForeignTable,
            Cols.name ForeignColumn
FROM        sys.foreign_key_columns FKCols

LEFT JOIN   sys.foreign_keys FKs
ON          FKCols.constraint_object_id = FKs.object_id

LEFT JOIN   sys.columns Cols
ON          Cols.object_id = FKs.referenced_object_id

WHERE       Cols.column_id = FKCols.referenced_column_id
AND         FKs.name = 'FK_Work_Tasks'

Which produces this:

It's taken me a few hours to get to this point, but I'm really stuck. I'm trying to get a result like this:

How can I merge these queries together?

I was going to post this to DBA, but I wasn't sure if this falls under the remit of a basic or advanced SQL query (off-topic/on-topic).

Also, if anyone can point out any optimisations I could use here, that would be very much appreciated too!

Andomar · Answer 1 · 2013-09-08T13:29:36.077

Note that a key can consist of multiple columns. You can't say that a column is the primary key, you can only say that the column is part of the key. This is true for both primary and foreign keys.

Here's an example using only sys.* views:

select  col.name
,       col_type.name
,       case when tab_pk_col.column_id is not null then 1 else 0 end as PartOfPrimaryKey
,       col.is_identity as [Identity]
,       col.is_nullable as Nullable
,       col.is_computed as Computed
,       tab_fk.name as PartOfForeignKey
,       ref.name as ForeignTable
,       ref_col.name as ForeignColumn
from    sys.tables tab -- Examined table
left join sys.columns col -- Columns of examined table
on      col.object_id = tab.object_id
left join sys.types col_type -- Type of column
on      col.system_type_id = col_type.system_type_id
left join sys.key_constraints tab_pk -- Primary keys
on      tab_pk.parent_object_id = tab.object_id
        and tab_pk.type = 'pk'
left join sys.index_columns tab_pk_col -- Columns in primary key
on      tab_pk_col.object_id = tab_pk.parent_object_id
        and tab_pk_col.index_id = tab_pk.unique_index_id
        and tab_pk_col.column_id = col.column_id
left join sys.foreign_key_columns tab_fk_col -- Columns in foreign keys
on      tab_fk_col.parent_object_id = tab.object_id
        and tab_fk_col.parent_column_id = col.column_id
left join sys.foreign_keys tab_fk -- Foreign keys
on      tab_fk.object_id = tab_fk_col.constraint_object_id
        and tab_fk.type = 'f'
left join sys.columns ref_col -- Columns referenced by column in foreign key
on      ref_col.object_id = tab_fk_col.referenced_object_id
        and ref_col.column_id = tab_fk_col.referenced_column_id
left join sys.tables ref -- Table name of referenced column
on      ref.object_id = ref_col.object_id
where   tab.name = 'table3'

See it working at SQL Fiddle.

Thanks for all your effort! It's duplicating each column though: http://i.imgur.com/M3Fkvew.png — Danny Beckett, Sep 08 '13 at 13:19
Thanks. There was one row for each foreign key, regardless of whether that column was part of that foreign key. I've improved the answer, it should show only single rows now. — Andomar, Sep 08 '13 at 13:30

score 0 · Answer 2 · answered Sep 08 '13 at 10:43

To pull in the foreign column data you want (ForeignTable and ForeignColumn), you can use a slightly more complex join on sys.foreign_key_columns to get both.

Modified SQL

SELECT      Cols.Name,
            TYPE_NAME(Cols.user_type_id) Type,
            CAST(ISNULL(OBJECTPROPERTY(OBJECT_ID(Keys.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) AS BIT) PrimaryKey,
            Cols.is_identity [Identity],
            Cols.is_nullable Nullable,
            --specified Cols.object_id here to remove ambiguous column error
            CAST(ColumnProperty(Cols.object_id, Cols.name, 'IsComputed') AS BIT) Computed, 
            CASE WHEN Cons.CONSTRAINT_TYPE IS NULL THEN NULL ELSE Keys.CONSTRAINT_NAME END ForeignKey,
            --new FK info columns
            OBJECT_NAME(FKCols.referenced_object_id) ForeignTable,
            FKColsInfo.name as ForeignColumn
FROM        sys.columns Cols

LEFT JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE Keys
ON          Keys.COLUMN_NAME = Cols.name
AND         Keys.TABLE_CATALOG = 'Tracker'
AND         Keys.TABLE_NAME = 'Work'

LEFT JOIN   INFORMATION_SCHEMA.TABLE_CONSTRAINTS Cons
ON          Cons.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
AND         Cons.CONSTRAINT_TYPE = 'FOREIGN KEY'

--JOIN that will fetch both the foreign key table name and the associated column name in that table
LEFT JOIN   (
                sys.foreign_key_columns FKCols LEFT JOIN sys.columns FKColsInfo 
                ON FKColsInfo.object_id = FKCols.referenced_object_id
                AND FKColsInfo.column_id = FKCols.referenced_column_id
            )
ON          FKCols.parent_object_id = Cols.object_id
AND         FKCols.parent_column_id = Cols.column_id

WHERE       Cols.object_id = OBJECT_ID('Work')
ORDER BY    Cols.column_id ASC

How can I merge these 2 queries together?

2 Answers2