Home » How to get all tables those are having a primary key

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 having 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 we have created three tables , first two tables TBL1 and TBL2 are having primary key , and the third table TBL3 is not having any primary key.

Get all tables those are having primary key

Following statement returns all 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 we have created with primary key are there in result set.

Lets understand about the functions that we have used in this query statement.

Here we have used SQL Server MetaData function OBJECTPROPERTY .

This function accepts two argument as ID and property as

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 we can get all tables which are not having primary key just replace 1 with 0 in where condition check.

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 , TBL3 that we have created without primary key also there in list.




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.