SQL Query to get misc column information

Question

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

Answer 1

try my query (i have pk_name and fk_name in separated column, so don't need case), it is on system views, and it is fast:

with 
  pk as (select pki.object_id, pki.column_id, _pk.name 
      from sys.index_columns pki  
      join sys.key_constraints _pk 
      on _pk.unique_index_id = pki.index_id and _pk.parent_object_id = pki.object_id
      where 1=1),
  fk as (select fkc.parent_object_id, fkc.parent_column_id, fk.name name, pkt.name pk_table, pkc.name pk_column, pkc.object_id, pkc.column_id
    from sys.foreign_keys as fk
    join sys.tables pkt
    on pkt.object_id = fk.referenced_object_id
    join sys.foreign_key_columns as fkc
    on fkc.constraint_object_id = fk.object_id
    join sys.columns as pkc
    on pkc.object_id = fkc.referenced_object_id and pkc.column_id = fkc.referenced_column_id
    where 1=1)
select t.name TableName
, t.object_id TableObjectId
, c.column_id CId
, c.name AS ColumnName
, typ.name AS ColumnDataType
, c.is_identity
, c.precision
, c.scale
, pk.name pk_name
, fk.name fk_name
, fk.pk_table
, fk.pk_column
, fkpk.name pk_for_fk
from sys.tables as t
inner join sys.columns as c on t.object_id = c.object_id
inner join sys.types as typ on typ.user_type_id = c.user_type_id
left join pk on pk.object_id = t.object_id and pk.column_id = c.column_id
left join fk on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
left join pk as fkpk on fkpk.object_id = fk.object_id and fkpk.column_id = fk.column_id
WHERE t.name = 'Products'

Answer 2

but it looks a bit overkill

If you want to pull a lot of values from a lot of tables then you are going to end up with a large query. That's just how it works. As these things go, this one isn't that large.

Are you concerned that SQL Server can't handle it? Don't be, it can. Performance? Not a lot you can do since these are internal catalog tables. Refactoring options are limited since you require a single statement and SPs are out. Wrapping it as an inline table valued function may help, but may hurt performance if it goes wrong.

If you just want clarity in the presentation of the SQL, the sub-queries could be written as CTEs, converted to views (or functions, but don't) or unnested so all joins are at the same indentation level. The latter is more likely to obscure than to elucidate, however.

On balance I think your best hope is to write clean code - good indentation, consistent naming, reasonable aliases etc. - and describe objectives and techniques in comments. What you have presented achieves most of this.