Dynamic SQL Query to find the count of missing values for all column of a table

user_7966057 0
Viewed 551

I want to write a dynamic sql query to find out the count of missing value for each column from a table. The table comprises of 40 columns and writing missing count for each column is lengthy so can we do it dynamically? I tried to write dynamic query as provided below, but got an error as

Must declare the scalar variable "@sql"

Query:

Declare @sql nvarchar(max)  
Declare @columnlist nvarchar(max)   
Declare @FieldName nvarchar(max)    

set @columnlist = 'Column 1,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 ,Column11 ,Column12,Column13'  

set @FieldName = 'Column 1,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ,Column7 ,Column8 ,Column9 ,Column10 ,Column11 ,Column12,Column13'

set @sql = 'SELECT COUNT(*)-COUNT(' + @columnlist + ') as '+ @FieldName +'from table_name' 

exec (@sql)
sql-server
2 Answers

Try this:

DECLARE @sql nvarchar(max) = N'SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName'

SELECT @sql = @sql + ' COUNT(*)-COUNT(' + QUOTENAME(COLUMN_NAME) + ') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name

EXEC (@sql)

Added

Allright, so here's an example on how to use something alike to for each column calculate the number of values that have at least one duplicate:

DECLARE @sql nvarchar(max) = N'WITH duplicates AS (SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName';

SELECT @sql = @sql 
  + N' CASE WHEN COUNT(' + QUOTENAME(COLUMN_NAME) 
  + N') OVER (PARTITION BY ' + QUOTENAME(COLUMN_NAME) 
  + N') > 1 THEN ' + QUOTENAME(COLUMN_NAME)  
  + N' END as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name + ') SELECT';

SELECT @sql = @sql 
  + N' COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) 
  + N') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from duplicates';

EXEC (@sql);

You see that it uses 2 SELECT statements, the first creates a CTE that replaces all values that have no duplicate with NULL:

CASE WHEN COUNT([C1]) OVER (PARTITION BY [C1]) > 1 THEN [C1] END as [C1]

The second uses a COUNT DISTINCT to count what is left. Doing it this way, the value NULL will not be counted.

user_67778390

Try this

IF OBJECT_ID('TempDb..#MissingList') IS NOT NULL
    DROP TABLE #MissingList

CREATE TABLE #MissingList
(
    SeqNo INT IDENTITY(1,1),
    TABLE_NAME VARCHAR(255),
    COLUMN_NAME VARCHAR(255),
    MissingCount INT DEFAULT(0)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT
    @SQL = REPLACE(REPLACE(L.List,'&#x0D',''),';','')
    FROM
    (
        SELECT 
            '
            INSERT INTO #MissingList(TABLE_NAME,COLUMN_NAME,MissingCount)
            SELECT TABLE_NAME = ''['+LTRIM(RTRIM(TABLE_SCHEMA))+'].['+LTRIM(RTRIM(TABLE_NAME))+']'',COLUMN_NAME = '''+LTRIM(RTRIM(COLUMN_NAME))+''',MissingCount = COUNT(1) - COUNT(['+LTRIM(RTRIM(COLUMN_NAME))+'])
            FROM ['+LTRIM(RTRIM(TABLE_SCHEMA))+'].['+LTRIM(RTRIM(TABLE_NAME))+']' AS [text()]
            FROM INFORMATION_SCHEMA.COLUMNS
            FOR XML PATH('')
    )L(LIST)

EXEC(@SQL)

SELECT
    *
    FROM #MissingList
user_51061540
Related