sql server programming: database object hierarchy -- sql server programming

RE: database object hierarchy

Mark Williams
2/2/2006 12:18:28 PM

sql server programming:

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.

--
[quoted text, click to view]

Re: database object hierarchy

Mark Williams
2/2/2006 1:19:07 PM

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.

--
[quoted text, click to view]

Re: database object hierarchy

Aaron Bertrand [SQL Server MVP]
2/2/2006 4:11:10 PM

[quoted text, click to view]

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

database object hierarchy

GB
2/2/2006 8:02:15 PM

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

Re: database object hierarchy

GB
2/2/2006 9:03:17 PM

What about views?

GB

[quoted text, click to view]

Re: database object hierarchy

GB
2/2/2006 10:13:49 PM

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

[quoted text, click to view]

Re: database object hierarchy

Franklin Allen
2/2/2006 10:19:42 PM

Declare @ObjectName Varchar(100)
Set @ObjectName = 'AENTR'

-- +----------------------------------+ --
-- Print List Of Parent Tables --
-- +----------------------------------+ --
Select Distinct Mo.Name
[quoted text, click to view]
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
[quoted text, click to view]
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

Re: database object hierarchy

GB
2/3/2006 5:06:22 PM

It returns empty Name column...

GB

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.