SQLTeam.com | Weblogs | Forums

Relationship between tables


#1

Hi
Please let me know how to know the relationship between the tables in the database. Don't want the relationship diagram since Add related tables is greyed out in database diagram.
Thanks


#2
SELECT 
    C.CONSTRAINT_NAME [constraint_name] 
   ,C.TABLE_NAME [referencing_table_name] 
   ,KCU.COLUMN_NAME [referencing_column_name] 
   ,C2.TABLE_NAME [referenced_table_name] 
   ,KCU2.COLUMN_NAME [referenced_column_name]
   ,RC.DELETE_RULE delete_referential_action_desc 
   ,RC.UPDATE_RULE update_referential_action_desc

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
         ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
         ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
         ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
            AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
         ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
            AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
            AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

#3

Try this

Select
object_name(rkeyid) Parent_Table,
object_name(fkeyid) Child_Table,
object_name(constid) FKey_Name,
c1.name FKey_Col,
c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.


#4

Thanks Ahamed for your reply. Am getting empty result for this query


#5

Hi Mike

Thanks for your reply. But am getting empty results on executing the query.


#6

can you post the screenshot.
I am guessing that you are running the query on m,aster database?


#7

Its in my application database and not in master.


#8

Is this database created by you or installed by the application automatically?
Are there Primary key and foreign key relations between tables?