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 [duplicate]

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