How to get list of Primary Key Constraint from all the databases on SQL Server Instance - SQL Server / TSQL Tutorial Part 60

Scenario:

You are working on documentation of database objects and you are asked to provide the list of all the primary key constraint on all the database tables on entire SQL Server Instance. How would you provide the list of all primary key constraints with Database Name, Schema Name, Table Name, Column Name, Constraint Name?

Solution:

We will be using System views to get the Primary Key Constraint Information. As we need to run the query on all the database , we will be using Cursor with dynamic sql. For each database , we will insert the results in temp table and finally we will select the result at the end from temp table for display.

USE master
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(500)
--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (
    ServerName VARCHAR(128)
    ,DatabaseName VARCHAR(128)
    ,SchemaName VARCHAR(128)
    ,TableName VARCHAR(128)
    ,ColumnName VARCHAR(128)
    ,ConstraintName VARCHAR(128)
    )
DECLARE CUR CURSOR
FOR
SELECT '[' + NAME + ']' AS DBName
FROM sys.databases
WHERE NAME NOT IN (
        'master'
        ,'tempdb'
        ,'model'
        ,'msdb'
        )
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = 'Insert into #Results
      Select
      @@ServerName,
   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' + @DatabaseName + '.information_Schema.Table_Constraints TC  
INNER JOIN
   ' + @DatabaseName + 

'.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'''
    EXEC (@SQL)
    PRINT @SQL
    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur
--Select all records from temp table for Primary Key 

--Constraint Information

SELECT *
FROM #Results

I execute above query on my SQL Server Instance and got the list of all Primary Key Constraint with Database Name, Schema Name, Table Name, Column Name and Constraint Name as shown below.

How to get list of all Primary Key Constraints from each database from SQL Server Instance - SQL Server / T-SQL Tutorial