none
Dynamic query for Null field checking

    Question

  • Hi

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

     

    Regards

    Ram

    Friday, August 27, 2010 11:11 AM

Answers

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


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Friday, August 27, 2010 12:48 PM
    Moderator
  •  

    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:

    http://blogs.msdn.com/b/jenss/archive/2010/08/28/get-the-null-percentage-of-values-in-a-specific-table-dynamically.aspx

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Saturday, August 28, 2010 10:42 PM
    Moderator
  • 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 %]
    '
    FROM INFORMATION_SCHEMA.COLUMNS
    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
    Moderator
  • 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)
    	,@SQL NVARCHAR(MAX)
    
    SELECT @TotalCount = COUNT(*)
    FROM [AdventureWorks2012].[Production].[Product]
    
    SELECT @SQL = STUFF((
    			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 %]
    '
    			FROM INFORMATION_SCHEMA.COLUMNS C
    			WHERE TABLE_NAME = 'Product'
    				AND TABLE_SCHEMA = 'Production'
    			ORDER BY C.ORDINAL_POSITION
    			FOR XML PATH('')
    				,type
    			).value('.', 'nvarchar(max)'), 1, 2, '')
    
    SET @SQL = 'SET @TotalCount = NULLIF(@TotalCount,0) 
    SELECT ' + @SQL + '
    FROM [AdventureWorks2012].Production.Product'
    
    PRINT @SQL
    
    EXECUTE SP_EXECUTESQL @SQL
    	,N'@TotalCount decimal(10,2)'
    	,@TotalCount


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


    My blog

    Sunday, September 09, 2012 6:17 PM
    Moderator