How to get list of Tables without Primary Key Constraint in SQL Server Database - SQL Server / T-SQL Tutorial Part 58

Scenario:

You are working as SQL Server developer for software development company. You are asked to provided list of Tables from a Database which do not have Primary Key Constraints created on them.

Solution:

There are different ways to get list of tables without primary key constraints in sql server database. In below query we are going to use system views to get List of tables in a database without Primary Key Constraints.

Select
   Table_CataLog as DatabaseName,
   Table_Schema as TableSchema,
   Table_Name as TableName      
from
   information_schema.tables T     
where
   Not Exists(
      Select
         1 
      from
         information_Schema.Table_Constraints C     
      where
         Constraint_Type='PRIMARY KEY'     
         and C.Table_Name=T.Table_Name     
         and C.Table_Schema=T.Table_Schema
   )     
   and Table_Type='BASE TABLE'

I execute above query on one of my database and got list of tables which do not have Primary Key Constraint created on them.

How to get list of tables without Primary Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial

Video Demo: How to get list of tables without Primary Key Constraint in SQL Server