question

RohitKochar-3979 avatar image
0 Votes"
RohitKochar-3979 asked BertZhoumsft-7490 edited

percentage of null column values

Hi Everyone,

I have a table with 600 columns and I would like to find percentage of null for every column. What is the best way to write a script without hardcoding the name of columns?



Thanks,

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@RohitKochar-3979

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Bert zhou

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 edited

One way

 Declare @sql nvarchar(max);
    
 select @sql = 'Select ' + String_Agg('Cast(100-100.*Count(' + QuoteName(c.name) + ')/Count(*) As decimal(5,2)) As ' + QuoteName(c.name), ',') + 'from ' + max(t.name)
 from sys.tables t
 Inner join sys.columns c on t.object_id = c.object_id
 where t.name = 'tblname'

Exec sp_executesql @sql;


Replace 'tblname' with the name of your table.

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
1 Vote"
BertZhoumsft-7490 answered

Hi,@RohitKochar-3979

Welcome to Microsoft T-SQL Q&A Forum!

This problem had a good solution many years ago, please check this link, it will help you.

Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
1 Vote"
JingyangLi answered

We had developed a few different solutions for this question 10 years ago. Hope you can find something useful to your question today:


[1]: https://docs.microsoft.com/en-us/archive/blogs/samlester/tsql-solve-it-your-way-finding-the-percentage-of-null-values-for-each-column-in-a-table

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.