CODEWITHSUNDEEP
sql-server
Joseph jun. Melettukunnel
Joseph jun. Melettukunnel

Reputation: 6397

SQL Server 2008: Find out primary/foreign key in Table?

Does anyone know how I can see which are the primary & foreign keys in a table?

EDIT: Thanks for all the responses. I was looking for a SQL Query to do that. Right now I'm playing around with writing a tool which can list me all Tables of a DB and show the columns. I'd like to display also which of the keys are primary keys.

This is how I read out the Table Catalog: 

const string sqlSelectTable = "SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
                                      "FROM INFORMATION_SCHEMA.TABLES " +
                                      "WHERE TABLE_TYPE = 'BASE TABLE' " +
                                      "ORDER BY TABLE_TYPE,TABLE_NAME";

And this is how I get the Infos about a Column:

const string sqlSelectTable =
            "SELECT     COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH " +
            "FROM         INFORMATION_SCHEMA.COLUMNS " +
            "WHERE     (TABLE_NAME = @TABLE_NAME) " +
            "ORDER BY ORDINAL_POSITION";

Would I have to create a Inner-Join so see which of the Columns are Primary Key?

Cheers

Upvotes: 5

Views: 29307

Answers (5)

Veer
Veer

Reputation: 1593

SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM 
sys.foreign_key_columns fkc 
INNER JOIN 
sys.columns c 
   ON fkc.parent_column_id = c.column_id 
      AND fkc.parent_object_id = c.object_id
INNER JOIN 
sys.columns cref 
   ON fkc.referenced_column_id = cref.column_id 
      AND fkc.referenced_object_id = cref.object_id  where OBJECT_NAME(parent_object_id) = 'tablename'

If you want to get the foreign key relation of all the tables exclude the where clause else write your tablename instead of tablename

Upvotes: 1

marc_s
marc_s

Reputation: 754438

For the primary key on each table, you can use this query:

SELECT
    kc.name,
    c.NAME
FROM 
    sys.key_constraints kc
INNER JOIN 
    sys.index_columns ic ON kc.parent_object_id = ic.object_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
    kc.type = 'PK'

and for the foreign key, I believe this query should get you the necessary information:

SELECT
    OBJECT_NAME(parent_object_id) 'Parent table',
    c.NAME 'Parent column name',
    OBJECT_NAME(referenced_object_id) 'Referenced table',
    cref.NAME 'Referenced column name'
FROM 
    sys.foreign_key_columns fkc
INNER JOIN 
    sys.columns c 
       ON fkc.parent_column_id = c.column_id 
          AND fkc.parent_object_id = c.object_id
INNER JOIN 
    sys.columns cref 
       ON fkc.referenced_column_id = cref.column_id 
          AND fkc.referenced_object_id = cref.object_id

Marc

Upvotes: 12

ax.
ax.

Reputation: 59927

see the Querying the SQL Server System Catalog FAQ, How do I find the columns of a primary key for a specified table? and How do I find the columns of a foreign key for a specified table?

EDIT: if you want to do it programmatically (which is not clear from your question), that is.

Upvotes: 4

Nestor
Nestor

Reputation: 13990

You can start with:

SELECT 
   Table_Name as [TableName], 
   Column_Name as [ColumnName],
   Constraint_Name as [Constraint], 
   Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY 
   [TableName], 
   [ColumnName]

(you can filter then by tableName)

Upvotes: 1

Guffa
Guffa

Reputation: 700302

In Management Studio, expand the table and then expand the Columns item. The primary key(s) has a key icon next to them.

To see the foreign keys, expand the Constraints item.

Upvotes: 3

Related Questions