There are often many ways to do the same (or similar) thing in SQL Server. Retrieving all primary keys from a database (or even just the primary key for a given table) is a case in point.
This article presents eleven different approaches to retrieving primary key information in SQL Server.
Some of these will be more useful than others, depending on your exact situation.
Example 1 – The sp_pkeys System Stored Procedure
If you just want the primary key for a given table, the sp_pkeys system stored procedure is probably the quickest and easiest way to do it. To do this, simply pass the table name to the procedure.
Like this:
EXEC sp_pkeys Artists;
Result (using vertical output):
TABLE_QUALIFIER | Music TABLE_OWNER | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistId KEY_SEQ | 1 PK_NAME | PK__Artists__25706B50FCD918B1
In this case I requested the primary key from the Artists table. It returns the primary key name (PK__Artists__25706B50FCD918B1), the database name (Music), the table name, the column name (ArtistId), plus other information.
Alternatively, I could have explicitly specified all arguments to get the same result:
EXEC sp_pkeys @table_name = Artists, @table_owner = dbo, @table_qualifier = Music;
Example 2 – The KEY_COLUMN_USAGE System View
The INFORMATION_SCHEMA.KEY_COLUMN_USAGE system view returns one row for each column that is constrained as a key in the current database. Therefore you’ll also get foreign keys returned with this view.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Albums | GenreId | FK_Albums_Genres | | Albums | AlbumId | PK__Albums__97B4BE379FC780BD | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | | Genres | GenreId | PK__Genres__0385057E88BB96F8 | +--------------+---------------+-------------------------------+
We can see that this returns constraints from all tables. We could also add a WHERE clause to narrow the results to a specific table.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Artists';
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | +--------------+---------------+-------------------------------+
In these examples, I narrowed the results to just those columns of the view that I’m interested in, but you can also use the asterisk (*) wildcard to return all columns.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Artists';
Result (using vertical output):
CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Artists__25706B50FCD918B1 TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistId ORDINAL_POSITION | 1
Example 3 – The CONSTRAINT_COLUMN_USAGE System View
This is similar to the previous example. The INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE system view returns one row for each column in the current database that has a constraint defined on the column. As with INFORMATION_SCHEMA.KEY_COLUMN_USAGE you’ll also get foreign keys returned with this view. However, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE is not limited to just key constraints.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Albums | GenreId | FK_Albums_Genres | | Albums | AlbumId | PK__Albums__97B4BE379FC780BD | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | | Genres | GenreId | PK__Genres__0385057E88BB96F8 | +--------------+---------------+-------------------------------+
You can use a WHERE clause to return only a specific table or column:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE COLUMN_NAME = 'ArtistId';
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | +--------------+---------------+-------------------------------+
In this case I specified a column name, so it returned all constraints on columns with that name. As it turns out, the database uses the same column name in two different tables. One has a foreign key constraint, the other a primary key. Therefore both rows are returned.
Again, you can use the asterisk (*) wildcard to return all columns.
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Artists';
Result (using vertical output):
TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistId CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Artists__25706B50FCD918B1
In this example I limited the output to just constraints on a specific table.
Example 4 – The CONSTRAINT_TABLE_USAGE System View
The INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE system view returns one row for each table in the current database that has a constraint defined on the table.
SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE;
Result:
+--------------+-------------------------------+ | TABLE_NAME | CONSTRAINT_NAME | |--------------+-------------------------------| | Artists | PK__Artists__25706B50FCD918B1 | | Genres | PK__Genres__0385057E88BB96F8 | | Albums | PK__Albums__97B4BE379FC780BD | | Albums | FK_Albums_Artists | | Albums | FK_Albums_Genres | +--------------+-------------------------------+
This view doesn’t return the column name/s that the constraints are used on, so it might not be as useful for you, depending on your requirements.
Here’s an example that shows all data returned by this view for the Genres table:
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE TABLE_NAME = 'Genres';
Result:
TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Genres CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Genres__0385057E88BB96F8
Example 5 – The TABLE_CONSTRAINTS System View
The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view returns one row for each table constraint in the current database.
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
Result:
+--------------+-------------------------------+-------------------+ | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | |--------------+-------------------------------+-------------------| | Artists | PK__Artists__25706B50FCD918B1 | PRIMARY KEY | | Genres | PK__Genres__0385057E88BB96F8 | PRIMARY KEY | | Albums | PK__Albums__97B4BE379FC780BD | PRIMARY KEY | | Albums | FK_Albums_Artists | FOREIGN KEY | | Albums | FK_Albums_Genres | FOREIGN KEY | +--------------+-------------------------------+-------------------+
This view doesn’t provide the column name, but it does provide the constraint type. Therefore, you can narrow the results to just primary keys:
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';
Result:
+--------------+-------------------------------+-------------------+ | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | |--------------+-------------------------------+-------------------| | Artists | PK__Artists__25706B50FCD918B1 | PRIMARY KEY | | Genres | PK__Genres__0385057E88BB96F8 | PRIMARY KEY | | Albums | PK__Albums__97B4BE379FC780BD | PRIMARY KEY | +--------------+-------------------------------+-------------------+
Here’s an example that uses a wildcard (*) character to return all data for constraints on the Artists table:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Artists';
Result:
CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Artists__25706B50FCD918B1 TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Artists CONSTRAINT_TYPE | PRIMARY KEY IS_DEFERRABLE | NO INITIALLY_DEFERRED | NO
Example 6 – The key_constraints System View
The sys.key_constraints system view contains a row for each object that is a primary key or unique constraint. Therefore we could use the following code to return all primary key constraints in the database, along with the relevant table for each primary key:
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name FROM sys.key_constraints WHERE type = 'PK';
Result:
+---------+-------------------------------+ | Table | name | |---------+-------------------------------| | Artists | PK__Artists__25706B50FCD918B1 | | Genres | PK__Genres__0385057E88BB96F8 | | Albums | PK__Albums__97B4BE379FC780BD | +---------+-------------------------------+
Of course, you can remove the WHERE clause to return both primary keys and unique keys.
The previous example uses the OBJECT_NAME() function to return the parent table name, rather than its object_id. If I didn’t use the OBJECT_NAME() function, the result would’ve looked like this:
SELECT parent_object_id, name FROM sys.key_constraints WHERE type = 'PK';
Result:
+--------------------+-------------------------------+ | parent_object_id | name | |--------------------+-------------------------------| | 885578193 | PK__Artists__25706B50FCD918B1 | | 917578307 | PK__Genres__0385057E88BB96F8 | | 949578421 | PK__Albums__97B4BE379FC780BD | +--------------------+-------------------------------+
The parent_object_id is returned instead of the parent object’s name. The OBJECT_NAME() function makes it much easier for us to understand which table each key is applied to.
Here’s an example where I use the wildcard (*) character to return all columns for the primary key on the Albums table:
SELECT * FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = 'Albums';
Result:
name | PK__Albums__97B4BE379FC780BD object_id | 965578478 principal_id | NULL schema_id | 1 parent_object_id | 949578421 type | PK type_desc | PRIMARY_KEY_CONSTRAINT create_date | 2019-05-02 12:56:06.377 modify_date | 2019-05-02 12:56:06.377 is_ms_shipped | 0 is_published | 0 is_schema_published | 0 unique_index_id | 1 is_system_named | 1 is_enforced | 1
Example 7 – The sys.objects System View
The sys.objects system view contains a row for each user-defined, schema-scoped object that is created within a database. So it contains our tables and primary keys.
We can take the previous query and replace key_constraints with objects and we’ll get the same result:
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name FROM sys.objects WHERE type = 'PK';
Result:
+---------+-------------------------------+ | Table | name | |---------+-------------------------------| | Artists | PK__Artists__25706B50FCD918B1 | | Genres | PK__Genres__0385057E88BB96F8 | | Albums | PK__Albums__97B4BE379FC780BD | +---------+-------------------------------+
Here’s an example that returns all data for the PK__Genres__0385057E88BB96F8 primary key:
SELECT * FROM sys.objects WHERE name = 'PK__Genres__0385057E88BB96F8';
Result (using vertical output):
name | PK__Genres__0385057E88BB96F8 object_id | 933578364 principal_id | NULL schema_id | 1 parent_object_id | 917578307 type | PK type_desc | PRIMARY_KEY_CONSTRAINT create_date | 2019-05-02 12:56:06.110 modify_date | 2019-05-02 12:56:06.110 is_ms_shipped | 0 is_published | 0 is_schema_published | 0
Example 8 – The REFERENTIAL_CONSTRAINTS System View
This one’s a bit of a stretch, but it could be useful depending on your situation. And technically, it does return (some) primary keys.
The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS system view returns one row for each FOREIGN KEY constraint in the current database. So its purpose is actually to return foreign keys – not primary keys. However, this view can be handy if you only want to see all primary keys that have an associated foreign key. It’s not a reliable method to use if you want to see all primary keys regardless of whether there’s an associated foreign key.
Here’s an example:
SELECT
CONSTRAINT_NAME,
UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
Result:
+-------------------+-------------------------------+ | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_NAME | |-------------------+-------------------------------| | FK_Albums_Artists | PK__Artists__25706B50FCD918B1 | | FK_Albums_Genres | PK__Genres__0385057E88BB96F8 | +-------------------+-------------------------------+
As you can see, this only returns those primary keys that have an associated foreign key. To be more precise, it’s actually the other way around – it returns all foreign keys, along with their associated UNIQUE constraint.
So if we compare these results with the results from previous examples, we can see that only two primary keys are returned (compared to three from previous examples). This is because one of the primary keys in this database (PK__Albums__97B4BE379FC780BD) doesn’t have an associated foreign key.
Here’s an example that returns all data for foreign keys of which PK__Artists__25706B50FCD918B1 is the UNIQUE constraint:
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE UNIQUE_CONSTRAINT_NAME = 'PK__Artists__25706B50FCD918B1';
Result:
CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists UNIQUE_CONSTRAINT_CATALOG | Music UNIQUE_CONSTRAINT_SCHEMA | dbo UNIQUE_CONSTRAINT_NAME | PK__Artists__25706B50FCD918B1 MATCH_OPTION | SIMPLE UPDATE_RULE | NO ACTION DELETE_RULE | NO ACTION
In this case, only one foreign key is returned.
As mentioned, this view is mainly for returning foreign key information, so only use this method if you want to see foreign key information along with the associated primary key. Don’t use this method if you want to see all primary keys regardless of whether they have an associated foreign key.
Example 9 – The sp_fkeys System Stored Procedure
This method is similar to the previous method, in the sense that it returns foreign key information. It also returns the primary key associated with each foreign key.
Example:
EXEC sp_fkeys Artists;
Result (using vertical output):
PKTABLE_QUALIFIER | Music PKTABLE_OWNER | dbo PKTABLE_NAME | Artists PKCOLUMN_NAME | ArtistId FKTABLE_QUALIFIER | Music FKTABLE_OWNER | dbo FKTABLE_NAME | Albums FKCOLUMN_NAME | ArtistId KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Albums_Artists PK_NAME | PK__Artists__25706B50FCD918B1 DEFERRABILITY | 7
I could have used the following syntax instead (or a combination of these arguments):
EXEC sp_fkeys @pktable_name = Artists, @pktable_owner = dbo, @pktable_qualifier = Music, @fktable_name = Albums, @fktable_owner = dbo, @fktable_qualifier = Music;
As with the previous example, this method should not be relied upon to return all primary keys. If a primary key doesn’t have an associated foreign key, it won’t be returned.
Example 10 – The sp_helpconstraint System Stored Procedure
The sp_helpconstraint system stored procedure returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only).
Therefore you can use it to return information on primary keys in your database.
Example:
EXEC sp_helpconstraint Albums;
Result:
+---------------+ | Object Name | |---------------| | Albums | +---------------+ -[ RECORD 1 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Artists delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | ArtistId -[ RECORD 2 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Artists (ArtistId) -[ RECORD 3 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Genres delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | GenreId -[ RECORD 4 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Genres (GenreId) -[ RECORD 5 ]------------------------- constraint_type | PRIMARY KEY (clustered) constraint_name | PK__Albums__97B4BE379FC780BD delete_action | (n/a) update_action | (n/a) status_enabled | (n/a) status_for_replication | (n/a) constraint_keys | AlbumId
Example 11 – The sp_help System Stored Procedure
The sp_help system stored procedure reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. This includes information about primary keys.
When you run this without any arguments, it provides information about summary information of objects of all types that exist in the current database is returned (i.e. a lot of information).
However, you can provide the name of a table, and it will return information about that table. Again, you’ll get a lot of information returned, but amongst this will be primary key information.
Example:
EXEC sp_help Artists;
Result:
+---------+---------+------------+-------------------------+ | Name | Owner | Type | Created_datetime | |---------+---------+------------+-------------------------| | Artists | dbo | user table | 2019-05-02 12:56:05.840 | +---------+---------+------------+-------------------------+ -[ RECORD 1 ]------------------------- Column_name | ArtistId Type | int Computed | no Length | 4 Prec | 10 Scale | 0 Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL -[ RECORD 2 ]------------------------- Column_name | ArtistName Type | nvarchar Computed | no Length | 510 Prec | Scale | Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | SQL_Latin1_General_CP1_CI_AS -[ RECORD 3 ]------------------------- Column_name | ActiveFrom Type | date Computed | no Length | 3 Prec | 10 Scale | 0 Nullable | yes TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL +------------+--------+-------------+-----------------------+ | Identity | Seed | Increment | Not For Replication | |------------+--------+-------------+-----------------------| | ArtistId | 1 | 1 | 0 | +------------+--------+-------------+-----------------------+ +-------------------------------+ | RowGuidCol | |-------------------------------| | No rowguidcol column defined. | +-------------------------------+ +-----------------------------+ | Data_located_on_filegroup | |-----------------------------| | PRIMARY | +-----------------------------+ +-------------------------------+---------------------------------------------------+--------------+ | index_name | index_description | index_keys | |-------------------------------+---------------------------------------------------+--------------| | PK__Artists__25706B50FCD918B1 | clustered, unique, primary key located on PRIMARY | ArtistId | +-------------------------------+---------------------------------------------------+--------------+ -[ RECORD 1 ]------------------------- constraint_type | PRIMARY KEY (clustered) constraint_name | PK__Artists__25706B50FCD918B1 delete_action | (n/a) update_action | (n/a) status_enabled | (n/a) status_for_replication | (n/a) constraint_keys | ArtistId +--------------------------------------+ | Table is referenced by foreign key | |--------------------------------------| | Music.dbo.Albums: FK_Albums_Artists | +--------------------------------------+
You can also provide the name of a primary key to get some basic information about it:
EXEC sp_help PK__Artists__25706B50FCD918B1;
Result:
+-------------------------------+---------+-----------------+-------------------------+ | Name | Owner | Type | Created_datetime | |-------------------------------+---------+-----------------+-------------------------| | PK__Artists__25706B50FCD918B1 | dbo | primary key cns | 2019-05-02 12:56:05.853 | +-------------------------------+---------+-----------------+-------------------------+