If you want to know if a table has a primary key or foreign key, you can find out this in many ways.
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
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
— find tables with FKs
select OBJECT_SCHEMA_NAME(f.parent_object_id)+’.’+OBJECT_NAME(f.parent_object_id) AS TableName
from sys.foreign_keys AS f
GLeb, Thanks for sharing.
I’d recommend sys.objects where type in (‘f’, ‘pk’). If you prefer to use a system proc, use sp_helpindex. Avoid INFORMATION_SCHEMA views like the plague, as they are slow and can contribute to deadlocks.
ScottPletcher – I know what you mean.