How to get all the Tables with or without Primary Key Constraint in Sql Server Database - SQL Server / TSQL Tutorial 59

Scenario:

You are working as SQL Server Developer / SQL Server DBA and you need to get list of tables from a database with information if the table has the primary key constraint or don't have primary key constraint.

Solution:

We are going to use system views in SQL server database to get list of tables with or without Primary Key Constraints.

use YourDatabaseName
go
Select
   T.Table_Catalog as DatabaseName,
   T.Table_Schema AS TableSchema,
   T.Table_Name AS TableName,
   CCU.Column_Name AS ColumnName,
   TC.Constraint_Name AS ConstraintName,
   Case When  TC.Constraint_Name is not Null Then 'Yes'
   Else 'No' End as HasPrimaryKeyConstraint
From
information_schema.tables T
left join 

   information_Schema.Table_Constraints TC 
   on T.Table_Catalog=TC.Table_Catalog
   and T.Table_Schema=TC.Table_Schema
   and T.Table_Name=TC.Table_Name
   and TC.Constraint_Type='PRIMARY KEY'
   
left JOIN
   Information_Schema.constraint_column_usage CCU  
      on TC.Constraint_Name=CCU.Constraint_Name  
      and TC.Table_Name=CCU.Table_Name
      and T.Table_Type='BASE TABLE'

I execute above code on one of my database and list of tables with Primary Key Constraint or Without Primary Key Constraint.

Get list of tables from SQL Server Database with or without Primary Key Constraint - SQL Server / TSQL Tutorial

Video Demo : How to get all the Tables with or without Primary Key Constraint in Sql Server Database