Oct 31, 2016

How to get all tables which have primary key in SQL Server?

Some time we need to get all tables which have primary key constraints. There are many way to get this information.


Select
       ISTC.Table_Catalog as BD_Name,
       ISTC.Table_Schema AS TBL_SCHEMA,
       ISTC.Table_Name AS TBL_NAME,
       ISCCU.Column_Name AS COL_NAME,
       ISTC.Constraint_Name AS CONSTRAINT_NAME
From
       information_Schema.Table_Constraints ISTC 
INNER JOIN
       Information_Schema.constraint_column_usage ISCCU 
       on ISTC.Constraint_Name=ISCCU.Constraint_Name 
       AND ISTC.Table_Name=ISCCU.Table_Name 
where
       Constraint_Type='PRIMARY KEY'




This one is the best query to find out the tables which have primary key because information schema provides more information and all the data can be retrieved with the filtered filter.