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

6 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?

  2. If you want to create Primary Keys on all tables in SQL Server that don’t have PKs, this SQL will generate all the PK create code.

    I use this on databases i inherit that are missing lots of keys…

    Hope this is of use!

    @nickhac

    select * ,
    ‘ALTER TABLE ‘ + SchemaName + ‘.’ + Tablename + ‘ ADD CONSTRAINT PK_’ + Tablename + ‘ PRIMARY KEY CLUSTERED (‘ +Table_Column_FirstWithIDInName + ‘)’ ScriptToCreatePrimaryKey

    from (
    SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName, OBJECT_NAME(OBJECT_ID) AS Tablename,
    (SELECT top 1 Column_Name FROM INFORMATION_SCHEMA.COLUMNS where table_name = T.name and column_name like ‘%ID%’ order by Ordinal_Position) Table_Column_FirstWithIDInName
    FROM SYS.Tables T
    WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0 AND type = ‘U’
    ) TblsWithoutPK

Leave a Reply

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