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.