SQL SERVER – Finding Tables with Primary or Foreign Keys

If you want to know if a table has a primary key or foreign key, you can find out this in many ways. Here are simple queries for finding tables with primary or foreign keys.

Let us create these tables

CREATE TABLE product_master
(
prod_id INT PRIMARY KEY,
prod_name VARCHAR(100),
price DECIMAL(12,2)
)
GO
CREATE TABLE product_details
(
prod_id INT, 
sales_date DATETIME,
sales_qty INT,
sales_amount DECIMAL(16,2)
)
GO
CREATE TABLE company_master
(
compnay_id INT,
company_name VARCHAR(100),
address VARCHAR(1000)
)
GO

Now let us create foreign key:

ALTER TABLE product_details ADD CONSTRAINT ck_item FOREIGN KEY(prod_id) REFERENCES product_master(prod_id)

Now if you want to check if a table has a primary key, you can use the following methods

1) Use sp_pkeys system stored procedure

EXEC sp_PKEYS product_master

The result is

2) Use OBJECTPROPERTY Function to Find Tables with Primary or Foreign Keys

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

if you want to check if a table has a foreign key, you can use the following method

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

The result is

If you want to check for the tables that do not have primary or foreign keys, you can use the following method

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=0 AND
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=0 AND
TABLE_TYPE='BASE TABLE'
ORDER BY
TABLE_NAME

The result is

A Safer and Newer Way

One caution about the queries above. OBJECT_ID(TABLE_NAME) uses a one part name, so SQL Server looks for the table only in your default schema and then in dbo. If a table lives in another schema, such as HumanResources in AdventureWorks, OBJECT_ID returns NULL and the table quietly drops out of the result. To be safe, pass the schema too: OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)).

These days I prefer the catalog views. sys.key_constraints lists primary key and unique constraints, and sys.foreign_keys lists foreign keys, each with its parent table:

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
       OBJECT_NAME(parent_object_id) AS TableName, name AS KeyName, type_desc
FROM sys.key_constraints
WHERE type = 'PK'
UNION ALL
SELECT OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), name, type_desc
FROM sys.foreign_keys;

This returns one row per key, so you see the schema, the table and the key name together. It is a quick way to review primary or foreign keys across a whole database before a migration. If a table shows up in neither list, that is a good moment to ask why it has no key at all.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.