How to get primarykey and foreign key info of a given table
-
Hi I have problem in getting primary and foreign key for a given table. For example in a Adventureworks Database, Production.ProductCostHistory table. If i try to get the foreign key related table it is not returning any value. But ProductID is is both primary and foreign key. How to get the foreign key table for the given table. sp_fkeys not working if it is a composite key. exec sp_fkeys @PKTable_name='ProductCostHistory',@PKTable_Owner='Production' Can any one help me please?........ kesavan
-
Hi I have problem in getting primary and foreign key for a given table. For example in a Adventureworks Database, Production.ProductCostHistory table. If i try to get the foreign key related table it is not returning any value. But ProductID is is both primary and foreign key. How to get the foreign key table for the given table. sp_fkeys not working if it is a composite key. exec sp_fkeys @PKTable_name='ProductCostHistory',@PKTable_Owner='Production' Can any one help me please?........ kesavan
If you have the correct rights you can select your table from sysobjects where [name] = 'yourtablename' You can use the id from that table to look in sysindexes for your primary keys. Then you can look in sysforeignkeys to see if you have some foreignkeys Hope that helps. Ben
-
If you have the correct rights you can select your table from sysobjects where [name] = 'yourtablename' You can use the id from that table to look in sysindexes for your primary keys. Then you can look in sysforeignkeys to see if you have some foreignkeys Hope that helps. Ben
-
can you give me any sample example for a column, it is both primary and foreign key please. kesavan
It is not that easy. First if you look in the sysobjects table for your table name to get the id. Next if you look in the sysobjects table for an xtype = 'PK' that is a primary key that has a parent_obj = the id you found above. This gives you your primary key Next you look in the sysforeignkeys table using the parentid joined back to sysobjects to try and find a foreignkey if it exists. So if you have a customer table and a customertype table Customer Table: customerId CustomertypeId ... other stuff CustomerType Table: customertypeId ... other stuff Here's a query: declare @id int select @id = id from sysobjects where [name] = 'customertype' select * from sysobjects where xtype = 'pk' and parent_obj = @id select * from sysforeignkeys fk join sysobjects so on fk.fkeyid = so.parent_obj and so.xtype = 'f' where fk.rkeyid = @id I don't know if that helps at all it is quite complicated. Ben
-
It is not that easy. First if you look in the sysobjects table for your table name to get the id. Next if you look in the sysobjects table for an xtype = 'PK' that is a primary key that has a parent_obj = the id you found above. This gives you your primary key Next you look in the sysforeignkeys table using the parentid joined back to sysobjects to try and find a foreignkey if it exists. So if you have a customer table and a customertype table Customer Table: customerId CustomertypeId ... other stuff CustomerType Table: customertypeId ... other stuff Here's a query: declare @id int select @id = id from sysobjects where [name] = 'customertype' select * from sysobjects where xtype = 'pk' and parent_obj = @id select * from sysforeignkeys fk join sysobjects so on fk.fkeyid = so.parent_obj and so.xtype = 'f' where fk.rkeyid = @id I don't know if that helps at all it is quite complicated. Ben
Thanks a lot ben I have done this by following way Select Tab1.Column_name,(select Schema_name(uid) from sys.sysobjects where id=PK_table_id)+'.'+Tab2.PKtable_name as PKTable_name, Tab2.PKColumn_name, Tab1.Table_Schema+'.'+Tab2.FKTable_Name as FKTable_Name, Tab2.FKColumn_Name from (Select Column_Name, Data_Type, Table_Schema from Information_schema.columns where Table_Name='Product' and Table_Schema='Production') Tab1 left outer join (select PKColumn_Name=c1.name,PKTable_Name=Convert(Sysname,Object_Name(f.refereced_Object_id)),PKTable_ID=f.referenced_Object_id,FKTable_Name=o1.name,FKColumn_Name=c2.name from sys.all_objects o1, sys.all_objects o2, sys.all_columns c1, sys.all_column c2, sys.foreign_keys f inner join sys.foreign_key_column k on(k.constraint_object_id=f.object_id) inner join sys.indexes i on (f.referenced_object_id=i.object_id and f.key_index_id=i.index_id) where o1.object_id=f.parent_object_id and o1.object_id=object_id('production.product') and o2.object_id=f.parent_object_id and c1.object_id=f.referenced_object_id and c2.object_id=f.parent_object_id and c1.column_id=k.referenced_column_id and c2.column_id=k.parent_column_id) Tab2 on Tab1.Column_name=Tab2.FKColumn_name kesavan