How to get list of Tables without Primary Key Constraint in all Databases of SQL Server Instance - SQL Server / TSQL Tutorial Part 62

Scenario:

You are working as SQL Server developer or SQL Server DBA. You need to get the list of all the tables from all the databases on SQL Server instance which do not have Primary Key Constraints. How would you do that?

Solution:

We will be using system views to get all the tables from all the databases on SQL Server Instance which do not have Primary Key Constraints.

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)
    )
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,
   Table_CataLog as DatabaseName,
   Table_Schema as TableSchema,
   Table_Name as TableName      
from
   '+@DatabaseName+'.information_schema.tables T     
where
   Not Exists(
      Select
         1 
      from
         '+@DatabaseName+'.information_Schema.Table_Constraints C     
      where
         Constraint_Type=''PRIMARY KEY''     
         and C.Table_Name=T.Table_Name     
         and C.Table_Schema=T.Table_Schema
   )     
   and 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 executed above query on my SQL Server Instance and got list of tables from all the databases which do not have Primary Key Constraints.

Get all the tables from SQL Server Instance which do not have Primary Key Constraint - SQL Server / TSQL Tutorial