best way to get related tables?

SQL Server 2000 : ASP w/ C# Codebehind 2.0 framework
I'm building an intranet site to query our database. ON the initial
page is a checkbox list of all the user tables in our DB. When a table
is first checked, I want to query the database and disable all
checkboxes representing tables not related to the first table checked.
The way I started to do it was to query
INFORMATION_SCHEMA.TABLE_CONSTRAINTS for contraints which contained the
table name, I then realized that even if I had a list of related tables
(which I could get by parsing the results properly from my I_S.T_C
query) I still wouldn't know which fields were related, which I need to
know in order to build the INNER JOIN part of the site's end query. So,
I suppose my question is, what is the easiest way to find out which
tables are related to a certain table, and on which fields those tables
are joined?
Thanks,
KrBIs this of any help?
SELECT u.column_name as PrimaryColumn,tc.TABLE_NAME AS PrimaryKeyTable,
--tc.CONSTRAINT_NAME AS PrimaryKey,
u2.column_name as ForeignColumn,
--COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on tc.CONSTRAINT_NAME
=u.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON
tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME
=rc1.CONSTRAINT_NAME
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u2 on rc1.CONSTRAINT_NAME
=u2.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I'll look into it, thanks SQL Menace =)
K|||That is really close to what I need. The only thing is ForeignColumn
and PrimaryColumn are always equal. I can tell it's picking up the
right tables, however. So it looks like u2.column_name is the same as
u.column_name. I'll tinker with it a bit, thanks again, Denis.|||If you run that code in the Northwind DB you will see that the column
names are not always the same, in pubs they are always the same
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hmm... Interesting. So is there a way to pull the foreign key column
out of this select?|||Here is a little addition
I have specified AND TC2.CONSTRAINT_TYPE ='FOREIGN KEY' just to be
sure
SELECT TC.CONSTRAINT_TYPE,TC2.CONSTRAINT_TYPE,u.column_name as
PrimaryColumn,tc.TABLE_NAME AS PrimaryKeyTable,
--tc.CONSTRAINT_NAME AS PrimaryKey,
u2.column_name as ForeignColumn,
--COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on tc.CONSTRAINT_NAME
=u.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON
tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME
=rc1.CONSTRAINT_NAME
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u2 on tc2.CONSTRAINT_NAME
=u2.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
AND TC2.CONSTRAINT_TYPE ='FOREIGN KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Okay... I think I was just being retarded. So it takes PrimaryKey from
PrimaryKeyTable and ForeignKeyTable is the related table, which is
where ForeignKey comes from. I thought, for some reason, it was saying
ForeignKey and PrimaryKey were coming from the same table... You've
surely saved me many hours of head to wall impact, thanks again.
Kyle