CODEWITHSUNDEEP
sqlsql-server
abs786123
abs786123

Reputation: 609

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

Upvotes: 1

Views: 750

Answers (1)

S3S
S3S

Reputation: 25112

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

Upvotes: 2

Related Questions