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

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 key or foreign key, 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

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