How can we know PK and FK relation if we have 200 tables in a database of SQL Server. Is there any query to check it

Question

Apart from database architecture diagram, is there any way to get the list of all the tables with PK/FK relations?

Answer 1

The simplest way is to inspect the system catalog views - try this for instance:

SELECT
    BaseTable = t.name,
    ForeignKeyConstraint = fk.name,
    ReferencedTable = ref.name
FROM
    sys.tables t
INNER JOIN 
    sys.foreign_keys fk ON fk.parent_object_id = t.object_id
INNER JOIN 
    sys.tables ref ON fk.referenced_object_id = ref.object_id

This will list the "base" tables, the name of the foreign key constraint, and the referenced table - here from the AdventureWorks sample database:

BaseTable	ForeignKeyConstraint	ReferencedTable
SalesTerritoryHistory	FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID	SalesPerson
Store	FK_Store_SalesPerson_SalesPersonID	SalesPerson
SalesOrderHeader	FK_SalesOrderHeader_SalesPerson_SalesPersonID	SalesPerson
SalesPersonQuotaHistory	FK_SalesPersonQuotaHistory_SalesPerson_BusinessEntityID	SalesPerson
ProductModelIllustration	FK_ProductModelIllustration_Illustration_IllustrationID	Illustration
WorkOrderRouting	FK_WorkOrderRouting_Location_LocationID	Location
ProductInventory	FK_ProductInventory_Location_LocationID	Location

(and so forth)

You can extend this by further inspecting the columns involved in those foreign key constraints - check the official MS documentation on system catalog views for more details