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

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

  1. you can use this query to know which have Primary Key

    SELECT i.name AS IndexName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
    FROM sys.indexes AS i INNER JOIN
    sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
    AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1

    1. hello.
      thanks for sharing the script ..
      got the list of tables .. and then what is the next step? as I got 3295 rows!

      1. Awesome stuff, now I have a quick question. I list the tables next to the fields that conform the primary key and its data type, is this possible?

Leave a Reply

Your email address will not be published. Required fields are marked *