Using SQL Server Management Studio to get a list of all tables and their Primary Keys

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 a lot of tables and need to find the relationship between the tables 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.

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 find what tables it is used in as a Foreign Key to that table. Use the article below to learn more.▼