SQL Script to Check Tables Defined With All Columns as NULL

Off late, I was asked to do a quick review of a database and it was one of the things that I always look for….generally, we don’t have these kind of tables in a production running database but it’s always good to check and you never know when you get surprised. Here is the small query that I executed to find all the tables with all of its columns defined as NULL –

-- List of Tables
-- With ALL its columns
-- Defined as NULLs

SELECT CONCAT(s.name, '.', o.name), * 
FROM sys.objects o 
INNER JOIN sys.schemas s
	ON o.schema_id = s.schema_id
WHERE o.type = 'U'
AND NOT EXISTS
(
	SELECT * FROM sys.columns c WHERE c.is_nullable = 0 AND o.object_id = c.object_id
)