Wednesday, April 22, 2009

Get Primary key - Foreign key relations table in sql server 2005

Get Primary key - Foreign key relations table in sql server 2005 Why it is required? The main purpose of this is finding related constraint table from database. Check below Query....


--get table list with constraint(primary and foreign key)  

select * from information_schema.constraint_column_usage  

--get table list with foreign key constraint  
select * from information_schema.referential_constraints  

--get the relation  
select tblAll.table_name as PrimaryTableName, tblAll.column_name as PrimaryTableColumn, 
tblFK.table_name as ForeignKeyTable, tblFK.column_name as ForeignKeyColumn 
from information_schema.constraint_column_usage tblAll 
inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name 
inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name   

thnx

No comments:

Post a Comment