Get List of Tables with or without Primary Key Constraint in all Database from SQL Server Instance - SQL Server / TSQL Tutorial Part 61

Scenario:

You are working as SQL Server developer / SQL Server DBA for Insurance company. You are creating documentation for database objects. You need to get list of all tables from all the database from SQL Server Instance with out without Primary Key Constraints.

Solution:

We can use system views to list of tables with Primary Key Constraint or not from each database. As query has to run on each of the database to gather this information, we need to loop through list of user databases, we will be using cursor to perform looping through all the databases on SQL Server instance. We will build our dynamic sql for each database and save the results to temp table and finally we will select the records 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)
    ,HasPrimaryKeyConstraint VARCHAR(10)
    )

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,
   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
' + @DatabaseName + '.information_schema.tables T
left join 

   ' + @DatabaseName + '.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
   ' + @DatabaseName + 

'.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'''

    EXEC (@SQL)

    PRINT @SQL

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END

CLOSE Cur

DEALLOCATE Cur

--Select all records from temp table 
SELECT *
FROM #Results

I execute above query on my SQL Server instance and here are list of tables from all the databases with Primary Key constraint if available otherwise Null.

How to get all the tables with or without primary key Constraint in all the database from SQL Server Instance- SQL Server / TSQL Tutorial