Find LOB tables with no PRIMARY KEY

Question: How can I find and report on SQL Server tables with LOB columns but where the table has no PRIMARY KEY?    I'm classing an LOB column as 'TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY','NVARCHAR(MAX)' & 'VARCHAR(MAX)'.

I need this information for DMS & CDC processes.

Answer:  The main idea is list out the  LOB column tables & then check if they have a PRIMARY KEY associated.  This query completes this task . The query also checks across multiple schemas across a database.

SELECT TABLE_SCHEMA + '.' + TABLE_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE 

 
(data_type IN   

    ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')  

    OR  

    (data_type = 'VARCHAR' and character_maximum_length = -1)  

    OR  

    (data_type = 'NVARCHAR' and character_maximum_length = -1) 

) 

AND  

TABLE_SCHEMA + '.' + TABLE_NAME NOT IN 

(select  TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 

) 

More scripts for SQL Server Object Management 

List Foreign Keys referencing tables in SQL Server

What are the triggers in SQL Server

How to find all identity columns in SQL Server

How to find default values of all SQL Server columns

How to find computed columns on a SQL Server table

Find SQL Server LOB Columns

How to List all indexes of all SQL tables

How to find SQL Filtered Indexes