database object hierarchy
in [Programming SQL]
|
From: GB on 2 Feb 2006 15:02 Hello: In my SQL Server databases I have complex related tables and views. There is any tool which allow you to see (print) this complex hierarchy of views and tables as hierarchical tree? Thanks, GB
From: Mark Williams on 2 Feb 2006 15:18 You can create a Database Diagram, and check the box labeled "Add Related Tables automatically" to get an idea of how tables are related through foreign key constraints. You could also run this select t1.constraint_name, t2.table_name as "referencing table", t2.column_name AS "referencing column", t3.table_name AS "referenced table", t3.column_name AS "referenced column" from information_schema.referential_constraints t1 INNER JOIN information_schema.constraint_column_usage t2 ON t1.constraint_name = t2.constraint_name INNER JOIN information_schema.constraint_column_usage t3 ON t1.unique_constraint_name = t3.constraint_name order by t1.constraint_name which would produce a text listing of the foreign key relationships within a database. select * from information_schema.view_column_usage will give you an idea of what views are in the database, and which columns they reference in base tables. -- "GB" wrote: > Hello: > In my SQL Server databases I have complex related tables and views. > There is any tool which allow you to see (print) this complex hierarchy of > views and tables as hierarchical tree? > > Thanks, > GB > > >
From: GB on 2 Feb 2006 16:03 What about views? GB "Mark Williams" <MarkWilliams(a)discussions.microsoft.com> wrote in message news:15634DEA-89BC-42C9-9A89-0D36690F3116(a)microsoft.com... > You can create a Database Diagram, and check the box labeled "Add Related > Tables > automatically" to get an idea of how tables are related through foreign key > constraints. > > You could also run this > > select t1.constraint_name, t2.table_name as "referencing table", > t2.column_name AS "referencing column", > t3.table_name AS "referenced table", > t3.column_name AS "referenced column" > from information_schema.referential_constraints t1 > INNER JOIN information_schema.constraint_column_usage t2 ON > t1.constraint_name = t2.constraint_name > INNER JOIN information_schema.constraint_column_usage t3 ON > t1.unique_constraint_name = t3.constraint_name > order by t1.constraint_name > > which would produce a text listing of the foreign key relationships within a > database. > > select * from information_schema.view_column_usage > > will give you an idea of what views are in the database, and which columns > they reference in base tables. > > -- > "GB" wrote: > > > Hello: > > In my SQL Server databases I have complex related tables and views. > > There is any tool which allow you to see (print) this complex hierarchy of > > views and tables as hierarchical tree? > > > > Thanks, > > GB > > > > > >
From: Aaron Bertrand [SQL Server MVP] on 2 Feb 2006 16:11 > What about views? You're probably looking at something a lot more complex here, like ER software. You might want to check out some of these products: http://www.aspfaq.com/2209
From: Mark Williams on 2 Feb 2006 16:19
Views don't have foreign keys, so they would not show up in a database diagram or in the output of the first query. select view_name, table_name, column_name from information_schema.view_column_usage will list the views in your database, and which tables / columns they reference, but won't map for you which column in a view references which column(s) in base tables. The view information_schema.columns will list the columns that each view has, but there isn't a mapping between the columns in the base tables. -- "GB" wrote: > What about views? > > GB > > > "Mark Williams" <MarkWilliams(a)discussions.microsoft.com> wrote in message > news:15634DEA-89BC-42C9-9A89-0D36690F3116(a)microsoft.com... > > You can create a Database Diagram, and check the box labeled "Add Related > > Tables > > automatically" to get an idea of how tables are related through foreign > key > > constraints. > > > > You could also run this > > > > select t1.constraint_name, t2.table_name as "referencing table", > > t2.column_name AS "referencing column", > > t3.table_name AS "referenced table", > > t3.column_name AS "referenced column" > > from information_schema.referential_constraints t1 > > INNER JOIN information_schema.constraint_column_usage t2 ON > > t1.constraint_name = t2.constraint_name > > INNER JOIN information_schema.constraint_column_usage t3 ON > > t1.unique_constraint_name = t3.constraint_name > > order by t1.constraint_name > > > > which would produce a text listing of the foreign key relationships within > a > > database. > > > > select * from information_schema.view_column_usage > > > > will give you an idea of what views are in the database, and which columns > > they reference in base tables. > > > > -- > > "GB" wrote: > > > > > Hello: > > > In my SQL Server databases I have complex related tables and views. > > > There is any tool which allow you to see (print) this complex hierarchy > of > > > views and tables as hierarchical tree? > > > > > > Thanks, > > > GB > > > > > > > > > > > > |