List of all relations of databases table.

Hi,

How to get list of all relations of certein database's table?

Use Visual Studio for Databases and create a diagram|||

lupina:

Hi,

How to get list of all relations of certein database's table?

You can also create the diagram in SQL Server Management Studio admin permissions required, try the link below for a step by step guide. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

|||

Thanks for quick response, but I need to get this information through SQL query or SQLConnection object.

eg: that query return all tables with all constraints of current DB (unique keys and so on),
SELECT*FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGEAS COLS
INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME= CONS.CONSTRAINT_NAME

WHERE COLS.CONSTRAINT_CATALOG=DB_NAME()
ORDERBY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

but I don't know how get list of relations ( names of Table1.column_foreignKey1 related to Table2.Column_foreignKey2).

|||

To generate the relationship with the diagraming tool is simple but to get that info in code it gets very complicated, I have found two resources to take you in the right direction. I think registering is required in the second link because it is a forum post. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=164&messageid=117824

|||

I found the solution on one of the newsgroups:

create procedure usp_findreferences

@.tbname sysname=null

as

set nocount on

Print 'Referenced:'

select c1.table_name,

c1.column_name,

fkey=r.constraint_name,

referenced_parent_table=c2.table_name,

c2.column_name

from information_schema.constraint_column_usage c1 join

information_schema.referential_constraints r on

c1.constraint_name=r.constraint_name

join information_schema.constraint_column_usage c2 on

r.unique_constraint_name=c2.constraint_name

where c1.table_name=coalesce(@.tbname,c1.table_name)

order by case when @.tbname is null then c1.table_name else c2.table_name end

print ''

print 'Referencing:'

select c1.table_name,

c1.column_name,

fkey = r.constraint_name,

referencing_child_table = c2.table_name,

c2.column_name

from information_schema.constraint_column_usage c1 join

information_schema.referential_constraints r on

c1.constraint_name=r.unique_constraint_name

join information_schema.constraint_column_usage c2 on

r.constraint_name=c2.constraint_name

where c1.table_name=coalesce(@.tbname,c1.table_name)

order by case when @.tbname is null then c1.table_name else c2.table_name end

go

|||I glad to see you have found a solution, now you know it is not simple.