When working with a SQL Server database, it is often necessary to retrieve information about the primary keys and foreign keys in the database. This information can be useful for various purposes, such as understanding the database structure, analyzing relationships between tables, or generating create statements for the keys.

In SQL Server, there are two different methods to retrieve the list of primary keys and foreign keys from the database.

Method 1: INFORMATION_SCHEMA SELECT

The first method involves querying the INFORMATION_SCHEMA views. Here is an example query:

SELECT DISTINCT
    Constraint_Name AS [Constraint],
    Table_Schema AS [Schema],
    Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

This query retrieves the constraint name, schema name, and table name for both primary keys and foreign keys in the database.

Method 2: sys.objects SELECT

The second method involves querying the sys.objects view. Here is an example query:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    type_desc AS ConstraintType
FROM sys.objects 
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')

This query retrieves the name of the constraint, schema name, table name, and constraint type (primary key or foreign key) from the sys.objects view.

When comparing these two methods, it is clear that the sys schema provides more information and allows for more flexibility in querying the data. With the sys schema, we can easily distinguish between primary keys and foreign keys, and we can join this table with other tables to retrieve additional data.

Now, let’s take a small challenge. Can you modify both of these scripts to include the original definition of the key? In other words, can you generate a create statement for each primary key and foreign key? If you have a solution, please share it in the comments below. If I receive an appropriate answer, I will publish the solution on this blog with due credit.

Understanding the primary keys and foreign keys in a SQL Server database is essential for effective database management and development. By using the appropriate method to retrieve this information, you can gain valuable insights into the database structure and relationships between tables.