Tough Question!

[Vai2000]

Hi All, I have been given a task to display the schema of a target db (MS
SQL) and display its relationship visually on an ASPX Page. How should I
proceed with it
Any pointers, tips would be highly appreciated.

Thanks a lot

[Mark Williams]

You can do

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

to get a listing of all the foreign key constraints (relationships) within a
database. It gets tricky when there are composite foreign key constraints;
they will show up in the output of the above multiple times.

HTH,

-Mark Williams

[Martin C K Poon]

For SS2000, you can obtain schema information from the following system
tables of each database.
- syscolumns, sysobjects, systypes, sysindexkeys, sysindexes, sysreferences

- System Tables (SS2000 Books Online)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

--
Martin C K Poon
Senior Analyst Programmer
====================================
"Vai2000" <nos...@microsoft.com> 在郵件
news:%23ImI7qo...@TK2MSFTNGP03.phx.gbl 中撰寫...