#0198-SQL Server-How To: Retrieve Primary & Foreign Keys for any given table-Scripts


When documenting a database, one of the first things that is done is to document the list of tables in the database and the primary and foreign keys to establish a relationship between them.


As I was gathering my thoughts one evening, I realized that I knew 7 different methods to identify primary and 6 different methods to identify foreign keys within a database (Cool!). Today, I will share these methods with you.


The methods to identify (Primary & Foreign) Keys



  1. Using the system stored procedure sp_help to get the data definition information for any given table

    • The only issue I have with this method is that it brings back a lot of additional information

  2. Use the catalog view (specifically, object catalog view): sys.key_constraints

    • For foreign keys, one needs to use a combination of sys.foreign_key_columns and sys.columns

  3. Use the catalog view (specifically, object catalog view): sys.indexes

    • The reason this works is because defining a set of primary keys would, unless explicitly specified otherwise, create a clustered index on those columns
    • This method my not be valid for foreign keys as one may not have defined indexes on these columns

  4. Use the catalog view: sys.objects

    • Ultimately, a key is an object, and hence, an entry for the key has to exist in the sys.objects catalog view

  5. Query the INFORMATION_SCHEMA

    • Querying the INFORMATION_SCHEMA is recommended in environments where Microsoft SQL Server is not the only RDBMS system in the mix
    • Reason: INFORMATION_SCHEMA views are ISO compliant

  6. Use the system stored procedure: sys.sp_pkeys or sys.sp_fkeys
  7. Use the system stored procedure: sys.sp_primarykeys or sys.sp_foreignkeys

    • This method is only applicable for linked servers

7 methods to identify Primary Keys


Following is the set of scripts highlighting the 7 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

--Method 01 - Get the entire table definition
USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT * 
FROM sys.key_constraints AS kc 
WHERE kc.type = 'PK' 
  AND kc.is_ms_shipped = 0 
  AND kc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.indexes AS si 
WHERE si.type = 1 
  AND si.index_id = 1 
  AND si.is_primary_key = 1 
  AND si.object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'PK' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 05 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 06 - Use a system stored procedure
EXEC sys.sp_pkeys @table_name = 'Employee', 
                  @table_owner = 'HumanResources'
GO

----Method 07 - Works only on linked servers
--EXEC sys.sp_primarykeys @table_server = N'WIN2k8SQL2K12', 
--                        @table_name = N'Employee',
--                        @table_catalog = N'AdventureWorks2012', 
--                        @table_schema = N'HumanResources'
GO

6 methods to identify Foreign Keys


Following is the set of scripts highlighting the 6 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT OBJECT_NAME(Fkc.constraint_object_id) AS KeyName,
       Fkc.constraint_object_id,
       Fkc.constraint_column_id,
       Fkc.parent_object_id,
       Fkc.parent_column_id,
       Fkc.referenced_object_id,
       Fkc.referenced_column_id,
       sc.name
FROM sys.foreign_key_columns AS Fkc 
INNER JOIN sys.columns AS sc ON Fkc.parent_object_id = sc.object_id
                            AND Fkc.constraint_column_id = sc.column_id
WHERE Fkc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'F' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 05 - Use a system stored procedure
EXEC sys.sp_fkeys @pktable_name = 'Employee', 
                  @pktable_owner = 'HumanResources'
GO

----Method 06 - Works only on linked servers
--EXEC sys.sp_foreignkeys @table_server = N'WIN2k8SQL2K12', 
--                        @pktab_name = N'Employee',
--                        @pktab_catalog = N'AdventureWorks2012', 
--                        @pktab_schema = N'HumanResources'
--GO

Which method is recommended by Microsoft?


All the methods shown above are still valid (i.e. not marked as “deprecated” in SQL Server 2012). To the best of my knowledge, while there is no official documentation which explicitly states whether method A is better than method B; the KB195526 – How to Retrieve Primary Keys for SQL Server Tables recommends usage of the system stored procedure: sp_pkeys.


Which one do you use?


I would like to know which method is most used by you. Also, if there are any other methods that you use, and would like to share for the benefit of all, please do so in the comments.


References:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0198-SQL Server-How To: Retrieve Primary & Foreign Keys for any given table-Scripts

  1. bright

    I made a add-in for SQL Management Studio that can tell you the PK,
    FK (next to the column type, you see referenced table and column by FK),
    and other information like size (MB) per index, partitions, etc.

    It also has a search for objects by name part, from sql query window (just press ALT+1 while having cursor near your table name).
    ![screenshot][1]

    The address is: [www.sqlxdetails.com][2]

    [1]: http://www.sqlxdetails.com/web/sites/default/files/images/TableColumnsUnique.png
    [2]: http://www.sqlxdetails.com

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s