In [SQL Server Management Studio] ([SSMS]), we can use a script to obtain all tables' primary keys. This will come in handy if you have many tables and need to find relationships between them using their [Primary Key].

In SSMS, open a New Query editor.
Copy and paste this code into the editor.
Next, press [Execute] or [F5] to run the script.

CFFCS | CarrzSynEdit: | SQL Script


SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS PKName,
    c.name AS ColumnName
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.is_primary_key = 1
ORDER BY SchemaName, TableName, ic.key_ordinal;

The outcome will give results similar to this, using our Virtual Class Database.

dbo	Availability	PK_Availability	UNID
dbo	City	PK_City	CityID
dbo	CookieDemo	PK_CookieDemo	ID
dbo	Country	PK_Country	CountryID
dbo	DropDown	PK_DropDown	ddID
dbo	Dropdown2	PK_Dropdown2	InsertID
dbo	ImageServer	PK_ImageServer	PicsID
dbo	ImageUpload	PK_ImageUpload	PicsID
dbo	ISTag	PK_ISTag	ISKID
dbo	ISTags	PK_ISTags	ISKsID
dbo	LoadIt	PK_LoadIt	ID
dbo	MTime	PK_MTime	ID
dbo	Names	PK_Names	ID
dbo	NamesList	PK_NamesList	ID
dbo	Paging	PK_Paging	ID
dbo	Population	PK_Population	PopulationID
dbo	State	PK_State	StateID
dbo	Tags	PK_Tags	TagID
dbo	Teaching	PK_Teaching	ID
dbo	TestTable	PK_TestTable	TestID
dbo	XMLSitemap	PK_XML	XMLID
dbo	ZipTable	PK_ZipTable	ZipID

Once you have this information, you might want to check each Primary Key to see which tables it is used as a Foreign Key in. Use the article below to learn more.▼