How to Get all the Tables which has Primary Key Constraint Created in SQL Server Database - SQL Server / TSQL Tutorial 57

Scenario:

You are working as SQL Server Developer/ SQL Server DBA in Auto Insurance Company. You are asked to provide a query that should return list of all the tables with Schema Name, Column Name and Primary Key Constraint which has primary key constraint created on them.

Solution:

There are multiple ways to get this information. We are going to use system views to get all the tables with primary key constraints in SQL Server Database.

Select
   TC.Table_Catalog as DatabaseName,
   TC.Table_Schema AS TableSchema,
   TC.Table_Name AS TableName,
   CCU.Column_Name AS ColumnName,
   TC.Constraint_Name AS ConstraintName 
From
   information_Schema.Table_Constraints TC  
INNER JOIN
   Information_Schema.constraint_column_usage CCU  
      on TC.Constraint_Name=CCU.Constraint_Name  
      and TC.Table_Name=CCU.Table_Name  
where
   Constraint_Type='PRIMARY KEY'


I execute above query on one of my test database and here are my results with table names which has primary key constraints.

Get list of tables in SQL Server Database which has Primary Constraint - SQL Server/ TSQL Tutorial


Video Demo : Get list of Primary Key Constraints in SQL Server Database



No comments:

Post a Comment