TSQL 2K5 - How to get All parent tables followed by all child tables in one select

• iBar

  SSCertifiable

  Points: 5872

  More actions

  November 9, 2010 at 6:21 am

  #242705

  hello guys,

  I want to get parent-child PK-FK hierarchy in one select statement and one table output

  I want all parent tables (with PK which is using as FK in some other table) on top and next row should be its child table. And followed by their child if a child table has further tables under it.

  out put should be

  table_name, level

  department, 1-0 ..... (PK dept_id)

  employee, 2-1 ..... (FK dept_id)

  emp_history 3-1 ..... (FK empl_id)

  office,1-0 ..... (PK offi_id)

  locations,2-1 ..... (FK offi_id)

  I really dont want to put more effort on levels but still result should be hierarical like that.

  Any idea OR work aorund please ?

  Cheers.

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 9, 2010 at 8:49 am

  #1248357

  Hi

  Try With

  Select * from INFORMATION_SCHEMA.<view_name>

  this might have solutions for you

  Thanks

  Parthi

  Thanks
  Parthi

• iBar

  SSCertifiable

  Points: 5872

  More actions

  November 10, 2010 at 3:10 am

  #1248736

  I have asked how to use them and NOT what to use ?

  Any script/idea already avaiable please.

  Thanks.