Is there a data health table in SQL similar to Python?

1

Hello

In Python, you can run a simple command and it will display a chart with some data health statistics, e.g. percentages for NULLs etc.

Is there anything similar in SQL?

I am particularly looking to select specific columns and display the percentages and counts of the NULLs in these columns.

Thanks!

2 
declare @query varchar(max) , @tname varchar(1500), @cname varchar(1500)

drop table #stats
create table #stats(tablename varchar(1500), 
                    columnname varchar(1500),
					nullCount int
					)
-- declare cursor
DECLARE sqlfor_asks_too_many_questions CURSOR FOR
select t.name, c.name
from sys.tables t 
join sys.columns c 
    on t.object_id = c.object_id
 
-- open cursor
OPEN sqlfor_asks_too_many_questions;
 
-- loop through a cursor
FETCH NEXT FROM sqlfor_asks_too_many_questions INTO @tname, @cname;
WHILE @@FETCH_STATUS = 0
    BEGIN
    
	set @query = ' select ''' + @tname + ''' as tablename, ''' + @cname + ''' as columnname, _cnt ' +
	 'from ( select count(1) as _cnt from [' + @tname + '] where [' + @cname + '] is null ) a'

	--print @query
	insert into #stats
	exec(@query)

	FETCH NEXT FROM sqlfor_asks_too_many_questions INTO @tname, @cname;
    END;
 
-- close and deallocate cursor
CLOSE sqlfor_asks_too_many_questions;
DEALLOCATE sqlfor_asks_too_many_questions;


select * fROM #stats
1 Like
3

Thanks but not very efficient to be honest.

I was looking for something like:
SELECT
(SELECT COUNT(COL1) FROM TABLE) / (SELECT COUNT() FROM TABLE),
(SELECT COUNT(COL2) FROM TABLE) / (SELECT COUNT() FROM TABLE)
FROM TABLE

or along those lines.

4

mine might not be very efficient but yours seems even worse. what will you do when a new column or new table is added? sql is not python.

what you posted makes no sense. so if you have a table with 50 columns you are doing to do that 50 times? Can you show us how you would do it in python.

5

I was planning to get the list of columns with a SELECT TOP 1000 Rows built-in function and do a regex replace to convert this list to the code above.

6

Can you show us how ?