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
- 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
- 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
- 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
- The reason this works is because defining a set of primary keys would, unless explicitly specified otherwise, create a clustered index on those columns
- 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
- 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
- Querying the INFORMATION_SCHEMA is recommended in environments where Microsoft SQL Server is not the only RDBMS system in the mix
- Use the system stored procedure: sys.sp_pkeys or sys.sp_fkeys
- 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:
- KB195526 – How to Retrieve Primary Keys for SQL Server Tables
- System Stored Procedures:
- Catalog Views:
- INFORMATION_SCHEMA views
Until we meet next time,
SQLTwins by Nakul Vachhrajani 