CODEWITHSUNDEEP
sqlsql-server
Dave
Dave

Reputation: 253

Find out what is primary key and foreign key on a table

I am using the following statement to get back the details of a sql table and it works, however, I cannot seem to figure out how to find out what is the primary key field of the table does anybody know how to achieve this.

SELECT c.name Field, 
    t.name Type,
    c.max_length MaxLength,
    c.Precision, 
    c.Scale,
    c.is_nullable,
    c.collation_name,
    '"+ tableName + "' TableName FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE object_id = object_id('" + tableName + "') ORDER BY column_id";

Ideally what I would like is another field on the above table to determine if its a primary key or a foreign key is there a way to achieve this at all even if its a yes or no boolean field?.

I tell you for why I am building a reverse engineer table to xml platform for Sage 200 and to make the correct xml i need the above information

Upvotes: 1

Views: 127

Answers (3)

Evandro de Paula
Evandro de Paula

Reputation: 2642

Try the the query further below. Please note:

  • I only test it for the scenario described (Customer -> Address) by the illustration below;
  • Microsoft SQL Server version is Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 6.3 (Build 17763: );

enter image description here

SELECT
    C.name,
    C.column_id,
    C.object_id,
    C.max_length MaxLength,
    C.Precision, 
    C.Scale,
    C.is_nullable,
    C.collation_name,
    FK.name AS FK_name,
    KC.name AS PK_name
FROM
    sys.columns AS C
LEFT OUTER JOIN
    sys.foreign_key_columns AS FKC ON C.object_id = FKC.parent_object_id AND C.column_id = FKC.parent_column_id
LEFT OUTER JOIN
    sys.foreign_keys AS FK ON  FKC.constraint_object_id = FK.object_id
LEFT OUTER JOIN
    sys.key_constraints AS KC ON  C.object_id = KC.parent_object_id AND C.column_id = KC.unique_index_id
WHERE
    C.object_id = OBJECT_ID('<table name>')
ORDER BY
    C.column_id

--- Output for Customer table above ---

enter image description here

Upvotes: 2

saravanatn
saravanatn

Reputation: 630

TABLENAME indicates your table name

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLENAME'

Upvotes: 0

saravanatn
saravanatn

Reputation: 630

Highlight the table and then press ALT+F1 type. Scroll down to get PK and FK information

Upvotes: 0

Related Questions