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.

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

SQL SERVER - Finding Tables with Primary or Foreign Keys objprop1

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

SQL SERVER - Finding Tables with Primary or Foreign Keys objprop2
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

SQL SERVER - Finding Tables with Primary or Foreign Keys objprop3

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
SQL SERVER - Finding Tables with Primary or Foreign Keys objprop4

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Scripts
Previous Post
SQL SERVER – Collect and Analyze SQL Server Data Efficiently
Next Post
SQL SERVER – Recovering from Snapshots – Notes from the Field #078

Related Posts

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

    Reply
  • ScottPletcher
    April 29, 2015 9:25 pm

    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.

    Reply

Leave a Reply