Tough Question!  
Author Message
LeifRasmussen





PostPosted: Tue May 23 12:41:03 CDT 2006 Top

SQL Server Developer >> Tough Question!

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

SQL Server1  
 
 
MarkWilliams





PostPosted: Tue May 23 12:41:03 CDT 2006 Top

SQL Server Developer >> Tough Question! 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



> 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
>
>
>
 
 
Martin





PostPosted: Tue May 23 16:06:57 CDT 2006 Top

SQL Server Developer >> Tough Question! 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
====================================


> 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
>
>