Oct 31, 2016

How to get all tables without primary key in SQL Server?

There are many way to find tables without primary key one of them described below.


Select
   Table_CataLog as DB_NAME,
   Table_Schema as TBL_SCHEMA,
   Table_Name as TBL_NAME     
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'



 This query will fetch all tables’ information which do not have primary key.