CODEWITHSUNDEEP
t-sql
Lana B
Lana B

Reputation: 496

t-sql select column names from all tables where there is at least 1 null value

Context: I am exploring a new database (in MS SQL server), and I want to know for each table, all columns that have null values.

I.e. result would look something like this: 

table column nulls
Tbl1  Col1   8

I have found this code here on stackoverflow, that makes a table of table-columnnames - without the WHERE statement which is my addition. I tried to filter for nulls in WHERE statement, but then the table ends up empty, and I see why - i am checking if the col name is actually null, and not its contents. But can't figure out how to proceed. 

select schema_name(tab.schema_id) as schema_name,
tab.name as table_name, 
col.name as column_name

from sys.tables as tab
     inner join sys.columns as col
         on tab.object_id = col.object_id
     left join sys.types as t
         on col.user_type_id = t.user_type_id
-- in this where statement, I am trying to filter for nulls, but i get an empty result. and i know there are nulls
where col.name is null
order by schema_name, table_name, column_id

I also tried this (see 4th line):

select schema_name(tab.schema_id) as schema_name,
tab.name as table_name, 
col.name as column_name
,(select count(*) from tab.name where col.name is null) as countnulls

from sys.tables as tab
     inner join sys.columns as col
         on tab.object_id = col.object_id
     left join sys.types as t
         on col.user_type_id = t.user_type_id
order by schema_name, table_name, column_id

the last one returns an error "Invalid object name 'tab.name'."

Upvotes: 0

Views: 1275

Answers (1)

Ahmed Yousif
Ahmed Yousif

Reputation: 2348

column name can't be null but if you mean nullable column (column that accept null value) that has null value at least so you can use following statement:

declare @schema varchar(255), @table varchar(255),  @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT schema_name(tab.schema_id) as schema_name,tab.name , col.name from sys.tables as tab
     inner join sys.columns as col on tab.object_id = col.object_id
where col.is_nullable =1 
order by schema_name(tab.schema_id),tab.name,col.name

OPEN getinfo

FETCH NEXT FROM getinfo into @schema,@table,@col

WHILE @@FETCH_STATUS = 0
BEGIN

    set @schema  = QUOTENAME(@schema)
    set @table = QUOTENAME(@table)
    set @col =  QUOTENAME(@col)
    SELECT @cmd = 'IF EXISTS (SELECT 1 FROM '+ @schema +'.'+ @table +' WHERE ' + @col + ' IS NULL) BEGIN SELECT '''+@schema+''' as schemaName, '''+@table+''' as tablename, '''+@col+''' as columnName, * FROM '+ @schema +'.'+ @table +' WHERE ' + @col + ' IS NULL end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into  @schema,@table,@col
END

CLOSE getinfo
DEALLOCATE getinfo

that use cursor on all nullable columns in every table in the Database then check if this column has at least one null value if yes will select schema Name, table name, column name and all records that has null value in this column

but if you want to get only count of nulls you can use the following statement:

declare @schema varchar(255), @table varchar(255),  @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT schema_name(tab.schema_id) as schema_name,tab.name , col.name from sys.tables as tab
     inner join sys.columns as col on tab.object_id = col.object_id
where col.is_nullable =1 
order by schema_name(tab.schema_id),tab.name,col.name

OPEN getinfo

FETCH NEXT FROM getinfo into @schema,@table,@col

WHILE @@FETCH_STATUS = 0
BEGIN
    set @schema  = QUOTENAME(@schema)
    set @table = QUOTENAME(@table)
    set @col =  QUOTENAME(@col)
    SELECT @cmd = 'IF EXISTS (SELECT 1 FROM '+ @schema +'.'+ @table +' WHERE ' + @col + ' IS NULL) BEGIN SELECT '''+@schema+''' as schemaName, '''+@table+''' as tablename, '''+@col+''' as columnName, count(*) as nulls FROM '+ @schema +'.'+ @table +' WHERE ' + @col + ' IS NULL end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into  @schema,@table,@col
END

that use cursor on all nullable columns in every table in the Database then check if this column has at least one null value if yes will select schema Name, table name, column name and count all records that has null value in this column

Upvotes: 1

Related Questions