Tag Archives: Tables With No Primary Key

How to get all the Tables with or without Primary Key Constraint in Sql Server?

We can write a query like below to get all the Tables with no Primary key constraint:

SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'

We can write a query like below to get all the Tables with Primary key constraint:

SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
      AND type = 'U'

Let us understand this with example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Primary Key.*/
CREATE TABLE dbo.Customers (
	CustomerId int IDENTITY (1, 1) 
              PRIMARY KEY CLUSTERED NOT NULL ,
	FirstName Varchar(50),
	LastName Varchar(50))
GO

/*Let us create Orders Table without any primary key.*/
CREATE TABLE dbo.Orders (
	OrderId int IDENTITY (1, 1) NOT NULL ,
	CustomerId int NOT NULL ,
	CreationDT DATETIME NOT NULL)
GO

Now let us run the queries to get the list of all Tables with or without Primary Keys and verify the result:
List_All_Tables_With_And_Without_Primary_Keys