How to get all tables those are having a primary key

How to get the list of all primary key table in database?

Lets create a some sample tables with and without primary key.

CREATE TABLE TBL1 ( SNO INT PRIMARY KEY,
NAME VARCHAR(50))

CREATE TABLE TBL2 ( SNO INT PRIMARY KEY,
NAME VARCHAR(50))

CREATE TABLE TBL3 ( SNO INT ,
NAME VARCHAR(50))

As you can see, we have created three tables. First two tables TBL1 and TBL2 are having a primary key on column while the third table TBL3 does not have any primary key on column.

Get all tables those are having primary key

Following statement returns all the tables which are having primary key.

SELECT TBL.name As 'Table having primary key'
FROM SYS.Tables TBL 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
AND type = 'U'

As you can see, the tables TBL1 and TBL2 which which are having a primary key are there in result set.

Lets understand the above T-SQL, here we have used a SQL Server Meta Data function named OBJECTPROPERTY.

This function accepts two argument as ID and property as follows:

OBJECTPROPERTY(id, property)

• id  is a id of the object in the current database.
• Property is the information to be returned for the object specified by id.

To check for primary key the property value is ‘TableHasPrimaryKey’.

This function returns two values either 1, if table is having primary key or 0 , if table is not having any primary key.

As you can see, we have checked for value 1, it means we want to get all those tables which are having primary key.

In similar way, you can get all tables which are not having primary key. For this you just need to replace 1 with 0 in where condition.

Get all tables those are not having primary key

SELECT TBL.name As 'Table Not having primary key'
FROM SYS.Tables TBL 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
AND type = 'U' 

 

As you can see, this time it returns a list of tables those are not having a primary key that is TBL3.