If you ever find yourself needing to find all tables that have a primary key in SQL Server, this article can help.
This article offers seven ways to return all tables in the current database that have a primary key.
Note that most of these examples return only the tables – not the primary keys themselves. If you want a list of primary keys, see 11 Ways to Return a Primary Key in SQL Server.
Option 1 – OBJECTPROPERTY() with sys.tables
The first option involves using the OBJECTPROPERTY()
function when querying the sys.tables
system view. This function accepts a TableHasPrimaryKey
argument. If this argument has a value of 1
, we get all tables that have a primary key (if it’s 0
then we get all tables that don’t have a primary key).
SELECT SCHEMA_NAME(schema_id) AS [Schema], name AS [Table] FROM sys.tables WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 1 ORDER BY [Schema], [Table];
Result:
+----------+---------+ | Schema | Table | |----------+---------| | dbo | Albums | | dbo | Artists | | dbo | Country | | dbo | Genres | +----------+---------+
In this example, the current database has four tables with a primary key.
The remaining examples will query the same database, so the results will be the same for those queries.
Option 2 – OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES
This example uses OBJECTPROPERTY()
again, but this time I’m querying the INFORMATION_SCHEMA.TABLES
view.
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)),'TableHasPrimaryKey') = 1 AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME;
Result:
+----------------+--------------+ | TABLE_SCHEMA | TABLE_NAME | |----------------+--------------| | dbo | Albums | | dbo | Artists | | dbo | Country | | dbo | Genres | +----------------+--------------+
Option 3 – OBJECTPROPERTY() with sys.objects
Once again OBJECTPROPERTY()
comes to the rescue. This time I’m querying the sys.objects
system view.
SELECT SCHEMA_NAME(schema_id) AS [Schema], name AS [Table] FROM sys.objects WHERE type = 'U' AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasPrimaryKey') = 1 ORDER BY [Schema], [Table]
Result:
+----------+---------+ | Schema | Table | |----------+---------| | dbo | Albums | | dbo | Artists | | dbo | Country | | dbo | Genres | +----------+---------+
Option 4 – INFORMATION_SCHEMA.TABLE_CONSTRAINTS
You can query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS
view to get a list of tables with primary keys. You need to filter the results to only those rows that have a CONSTRAINT_TYPE
of PRIMARY KEY
.
SELECT CONSTRAINT_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';
Result:
+---------------------+--------------+ | CONSTRAINT_SCHEMA | TABLE_NAME | |---------------------+--------------| | dbo | Artists | | dbo | Genres | | dbo | Albums | | dbo | Country | +---------------------+--------------+
This view also returns the constraint name, so you can also include that column if required:
SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';
Result:
+---------------------+--------------+-------------------------------+ | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | |---------------------+--------------+-------------------------------| | dbo | Artists | PK__Artists__25706B50FCD918B1 | | dbo | Genres | PK__Genres__0385057E88BB96F8 | | dbo | Albums | PK__Albums__97B4BE379FC780BD | | dbo | Country | PK__Country__10D1609F97ADEC31 | +---------------------+--------------+-------------------------------+
Option 5 – sys.key_constraints
You can filter the sys.key_constraints
view a CONSTRAINT_TYPE
of PK
to get a list of tables with primary keys.
SELECT SCHEMA_NAME(schema_id) AS [Schema], OBJECT_NAME(parent_object_id) AS [Table] FROM sys.key_constraints WHERE type = 'PK';
Result:
+----------+---------+ | Schema | Table | |----------+---------| | dbo | Artists | | dbo | Genres | | dbo | Albums | | dbo | Country | +----------+---------+
Here it is again with the primary key name:
SELECT SCHEMA_NAME(schema_id) AS [Schema], OBJECT_NAME(parent_object_id) AS [Table], name FROM sys.key_constraints WHERE type = 'PK';
Result:
+----------+---------+-------------------------------+ | Schema | Table | name | |----------+---------+-------------------------------| | dbo | Artists | PK__Artists__25706B50FCD918B1 | | dbo | Genres | PK__Genres__0385057E88BB96F8 | | dbo | Albums | PK__Albums__97B4BE379FC780BD | | dbo | Country | PK__Country__10D1609F97ADEC31 | +----------+---------+-------------------------------+
Option 6 – sys.objects
The sys.objects
system view is a popular one for returning information about schema-scoped objects, including primary keys.
SELECT SCHEMA_NAME(schema_id) AS [Schema], OBJECT_NAME(parent_object_id) AS [Table] FROM sys.objects WHERE type = 'PK';
Result:
+----------+---------+ | Schema | Table | |----------+---------| | dbo | Artists | | dbo | Genres | | dbo | Albums | | dbo | Country | +----------+---------+
As with the previous two examples, we can include the name
column of this view to display the name of the primary key:
SELECT SCHEMA_NAME(schema_id) AS [Schema], OBJECT_NAME(parent_object_id) AS [Table], name FROM sys.objects WHERE type = 'PK';
Result:
+----------+---------+-------------------------------+ | Schema | Table | name | |----------+---------+-------------------------------| | dbo | Artists | PK__Artists__25706B50FCD918B1 | | dbo | Genres | PK__Genres__0385057E88BB96F8 | | dbo | Albums | PK__Albums__97B4BE379FC780BD | | dbo | Country | PK__Country__10D1609F97ADEC31 | +----------+---------+-------------------------------+
Option 7 – OBJECTPROPERTYEX()
The OBJECTPROPERTYEX()
function works just like the OBJECTPROPERTY()
function, except that it supports more properties. Therefore, any of the previous examples that use OBJECTPROPERTY()
, could easily be rewritten to use OBJECTPROPERTYEX()
.
For example, I could rewrite the first example on this page to the following:
SELECT SCHEMA_NAME(schema_id) AS [Schema], name AS [Table] FROM sys.tables WHERE OBJECTPROPERTYEX(object_id, 'TableHasPrimaryKey') = 1 ORDER BY [Schema], [Table];
Result:
+----------+---------+ | Schema | Table | |----------+---------| | dbo | Albums | | dbo | Artists | | dbo | Country | | dbo | Genres | +----------+---------+
I should mention that OBJECTPROPERTYEX()
returns a sql_variant data type, whereas OBJECTPROPERTY()
returns an int.