abs786123 abs786123 - 9 days ago 6
SQL Question

SQL count all nulls in every columns in all tables in a sys tables

I have the following SQL:

SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = 'dbo'
DECLARE @Table NVARCHAR(100) = NULL
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls

CREATE TABLE #Nulls (TableName sysname, ColumnName sysname, ColumnPosition int
, NullCount int , NonNullCount int)

SELECT @sql += 'SELECT '''+TABLE_NAME+''' AS TableName ,

'''+COLUMN_NAME+''' AS ColumnName, '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+'''AS ColumnPosition,
SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls ,
COUNT(' +COLUMN_NAME+') CountnonNulls FROM
'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

INSERT INTO #Nulls
EXEC sp_executesql @sql

SELECT *
FROM #Nulls


This goes through counting all nulls the way i expect it. However, I want to use this sql to loop over each tables in a catalogue for example in the

information.schema.table


Can someone please provide the sql required to do this, I am completely clueless with using cursors.

Thank you

Answer

No need for a cursor. Just set your @Table to NULL

DECLARE @Table NVARCHAR(100) = NULL

Then change your WHERE clause from this

WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table

to WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

So the entire code...

SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = 'dbo'
DECLARE @Table NVARCHAR(100) = NULL
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls

CREATE TABLE #Nulls (TableName sysname, ColumnName sysname, ColumnPosition int 
, NullCount int , NonNullCount int)

SELECT @sql += 'SELECT  '''+TABLE_NAME+''' AS TableName ,      

'''+COLUMN_NAME+''' AS ColumnName,  '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+'''AS ColumnPosition,     
SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls ,   
COUNT(' +COLUMN_NAME+') CountnonNulls FROM   
'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

INSERT INTO #Nulls 
EXEC sp_executesql @sql

SELECT * 
FROM #Nulls