percentage of null column values

Rohit Kochar 36 Reputation points
2022-05-05T21:40:05.517+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Bert Zhou-msft 3,421 Reputation points
    2022-05-06T01:17:01.237+00:00

    Hi,@Rohit Kochar

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2022-05-06T02:30:25.53+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  3. Tom Cooper 8,461 Reputation points
    2022-05-05T23:17:18.297+00:00

    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 @alenzi ;

    Replace 'tblname' with the name of your table.

    Tom

    0 comments No comments