sql - Get List of all columns of all tables and Reference table follwled by reference Key of table -

sql - Get List of all columns of all tables and Reference table follwled by reference Key of table -

hello have 50 tables in sql server.

all tables in join.

for ex.

country table

pkcountryid name

state table

pkstateid fkcountryid name

city table

pkcityid fkstateid name

employee table

pkid firstname lastname fkcityid

so want columns of tables. write below fiddle.

fiddle

this give me result

column_name

pkemployeeid name fkcityid

but want result this.

column_name

pkemployeeid name -fkcityid pkcityid name -fkstateid pkstateid name -fkcountryid pkcountryid name pkcityid name -fkstateid pkstateid name -fkcountryid pkcountryid name pkstateid name -fkcountryid pkcountryid name pkcountryid name

you can seek recursive mutual table expression. like:

; cte ( select c.name column_name , -- t.object_id tableid , 0 treelevel sys.tables t inner bring together sys.columns c on t.object_id = c.object_id t.object_id = 754101727 union select c.name ,-- + ' ' + f.referenced_column_name column_name , --t.object_id tableid , treelevel + 1 ( select f.name foreign_key_name , object_name(f.parent_object_id) table_name , col_name(fc.parent_object_id, fc.parent_column_id) constraint_column_name , object_name(f.referenced_object_id) referenced_object , col_name(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name , is_disabled , delete_referential_action_desc , update_referential_action_desc , f.referenced_object_id parent_object_id sys.foreign_keys f inner bring together sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id ) f bring together cte on cte.column_name = f.constraint_column_name bring together sys.tables t on t.object_id = f.parent_object_id inner bring together sys.columns c on t.object_id = c.object_id --in order stop loop @ specific level need: treelevel < 2 ) select --in order remove doublings add together distinct distinct * cte order treelevel, tableid

how recursive works can find here: sql server cte , recursion example

sql sql-server treeview