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.