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.




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 21 total views,  1 views today

Leave a Reply

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