SQL Query to get misc column information

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_fkfrom sys.tables as tinner join sys.columns as c on t.object_id = c.object_idinner join sys.types as typ on typ.user_type_id = c.user_type_idleft join pk on pk.object_id = t.object_id and pk.column_id = c.column_idleft join fk on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_idleft join pk as fkpk on fkpk.object_id = fk.object_id and fkpk.column_id = fk.column_idWHERE t.name = 'Products'

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.