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.
