Dynamic query for Null field checking


  • Hi

    I want dynamiclly find the table to null values finding.. Please give me quickly




    Friday, August 27, 2010 11:11 AM


All replies

  • ? WHat do you want, check one column in a table if it only contains NULL, check every row if there are NULLs container, etc. The requirement is not quite clear from your explanations.


    Jens K. Suessmeyer
    Friday, August 27, 2010 12:48 PM

    I want find the how much percentage of null values available in particular table that should dynamic query

    Friday, August 27, 2010 1:20 PM
  • Check the following script which I created for you and published to my blog:


    Jens K. Suessmeyer
    Saturday, August 28, 2010 10:42 PM
  • This script returns % of NULL values in each column of the table:

    use AdventureWorks 
    DECLARE @TotalCount decimal(10,2), @SQL NVARCHAR(MAX)
    SELECT @TotalCount = COUNT(*) FROM [AdventureWorks].[Production].[Product] 
    SELECT @SQL = 
    COALESCE(@SQL + ', ','SELECT ') +
    'cast(sum (case when ' + QUOTENAME(column_Name) + 
    ' IS NULL then 1 else 0 end)/@TotalCount*100.00 as decimal(10,2)) as [' +
    column_Name + ' NULL %]
    WHERE TABLE_NAME = 'Product' and TABLE_SCHEMA = 'Production'
    SET @SQL = 'set @TotalCount = NULLIF(@TotalCount,0) 
    ' + @SQL + '
    FROM [AdventureWorks].Production.Product'
    print @SQL
    EXECUTE SP_EXECUTESQL @SQL, N'@TotalCount decimal(10,2)', @TotalCount

    See my blog post (problem #4) here

    Interesting T-SQL problems


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Sunday, August 29, 2010 3:34 AM
  • Just want to explain the solution a little bit and provide a slightly different variation of it:

    1. To get the percent of null values in a column we use this formula

    sum(case when ColumnName IS NULL then 1 else 0 end) * 100.00 / @TotalCount

    where @TotalCount is the number of rows in a table.

    2. We need to generate the above statement dynamically, so we use INFORMATION_SCHEMA.Columns view to do so.

    3. I use FOR XML PATH('') approach to concatenate information into one variable

    4. I use sp_executeSQL system stored procedure to run the dynamic SQL in order to supply a variable. Alternatively, of course, I could have declared and calculated @TotalCount inside the dynamic SQL.

    USE AdventureWorks2012
    DECLARE @TotalCount DECIMAL(10, 2)
    SELECT @TotalCount = COUNT(*)
    FROM [AdventureWorks2012].[Production].[Product]
    			SELECT ', CAST(SUM(CASE WHEN ' + Quotename(C.COLUMN_NAME) + ' IS NULL THEN 1 ELSE 0 END) * 100.00 
    /@TotalCount AS decimal(10,2)) AS [' + C.COLUMN_NAME + ' NULL %]
    			WHERE TABLE_NAME = 'Product'
    				AND TABLE_SCHEMA = 'Production'
    			FOR XML PATH('')
    			).value('.', 'nvarchar(max)'), 1, 2, '')
    SET @SQL = 'SET @TotalCount = NULLIF(@TotalCount,0) 
    SELECT ' + @SQL + '
    FROM [AdventureWorks2012].Production.Product'
    	,N'@TotalCount decimal(10,2)'

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    Sunday, September 09, 2012 6:17 PM