1

Given a database, I would like a SQL command that would return a hierarchical list of all the tables. Now bear with me as I've never done SQL queries.

Before posting, I searched the web and found some solutions that were close enough but not quite what I wanted.

Like this one here: SQL Server: Howto get foreign key reference from information_schema?

SELECT  
 KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
,KCU1.TABLE_NAME AS FK_TABLE_NAME 
,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

It's good, but it's not recursive... it only shows the immediate parent table of the current table if any... not all its parents. And it doesn't show the table at all if it has no parent.

I found this as well: Hierarchical List of All tables

EXEC sp_msdependencies @intrans = 1

This shows the order of the dependencies of all tables, which I would like to have as well, but doesn't show the parent table. (and I read that this stored procedure is undocumented).

What I would like to have as the result would be something similar to this:

Level   Table Name    Parent Table
-----------------------------------
  1     Application
  1     System
  2     Users         Application 
  2     Roles         Application 
  2     Membership    Application
  3     Membership    Users
  3     UserInRoles   Users 
  3     UserInRoles   Roles

I would then read this from C#/VB.NET for a specific project I have

Can someone help?

Really appreciated

Community
  • 1
  • 1
MyGeekSide
  • 11
  • 2
  • possible duplicate of [Generate table relationship diagram from existing schema (SQL Server)](http://stackoverflow.com/questions/168724/generate-table-relationship-diagram-from-existing-schema-sql-server) If you look there, there are many tools listed including, what I think, will fit your needs. – T.S. Jun 10 '14 at 19:54
  • I don't think you'll be able to just write a single Sql statement to do what you want – T.S. Jun 10 '14 at 19:56
  • I don't want to see it using a tool... I want to get it trough code (C# or VB.NET) and do some stuff with it in my applicaiton. I would like to have the SQL query to call. Thanks – MyGeekSide Jun 10 '14 at 20:01
  • In this case, you will need to call few queries. You need to design related object and call `Information_schema.Tables`, `Information_schema.Columns`, etc. Connect the data and display as you wish. All you need to know is that you can find all you need in `information_schema` tables. What about `Crawler` project? I thought it is c# – T.S. Jun 10 '14 at 20:04

0 Answers0