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

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)

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

Menu
Exit mobile version