database object hierarchy
in [Programming SQL]
|
From: GB on 2 Feb 2006 17:13 This approach shows only relation between views of the same database only. If a view contains reference to another view from another database you can not see it. GB "Mark Williams" <MarkWilliams(a)discussions.microsoft.com> wrote in message news:9DC5764E-D2C1-41BF-9E81-4939328E4548(a)microsoft.com... > 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 > > > > > > > > > > > > > > > > > >
From: Franklin Allen on 3 Feb 2006 01:19 Declare @ObjectName Varchar(100) Set @ObjectName = 'AENTR' -- +----------------------------------+ -- -- Print List Of Parent Tables -- -- +----------------------------------+ -- Select Distinct Mo.Name >From Sysobjects Do, Sysobjects Mo, Syscolumns Mc, Syscolumns Dc, SysForeignkeys F Where Do.Name = @ObjectName And Mo.Id=Mc.Id And Do.Id=Dc.Id And Mo.Id=F.RkeyId And Do.Id=F.FkeyId And Mc.ColId=F.Rkey And Dc.ColId=F.Fkey -- +----------------------------------+ -- -- Print List Of Child Tables -- -- +----------------------------------+ -- Select Distinct Do.Name >From Sysobjects Do, Sysobjects Mo, Syscolumns Mc, Syscolumns Dc, SysForeignkeys F Where Mo.Name = @ObjectName And Mo.Id = Mc.Id And Do.Id = Dc.Id And Mo.Id = F.RkeyId And Do.Id = F.FkeyId And Mc.ColId = F.Rkey And Dc.ColId = F.Fkey
From: GB on 3 Feb 2006 12:06
It returns empty Name column... GB "Franklin Allen" <frank2allen(a)yahoo.com> wrote in message news:1138947582.771383.243340(a)f14g2000cwb.googlegroups.com... > Declare @ObjectName Varchar(100) > Set @ObjectName = 'AENTR' > > -- +----------------------------------+ -- > -- Print List Of Parent Tables -- > -- +----------------------------------+ -- > Select Distinct Mo.Name > >From Sysobjects Do, > Sysobjects Mo, > Syscolumns Mc, > Syscolumns Dc, > SysForeignkeys F > Where Do.Name = @ObjectName > And Mo.Id=Mc.Id > And Do.Id=Dc.Id > And Mo.Id=F.RkeyId > And Do.Id=F.FkeyId > And Mc.ColId=F.Rkey > And Dc.ColId=F.Fkey > > -- +----------------------------------+ -- > -- Print List Of Child Tables -- > -- +----------------------------------+ -- > Select Distinct Do.Name > >From Sysobjects Do, > Sysobjects Mo, > Syscolumns Mc, > Syscolumns Dc, > SysForeignkeys F > Where Mo.Name = @ObjectName > And Mo.Id = Mc.Id > And Do.Id = Dc.Id > And Mo.Id = F.RkeyId > And Do.Id = F.FkeyId > And Mc.ColId = F.Rkey > And Dc.ColId = F.Fkey > |