Finding Columns Containing NULLs: Techniques in SQL Server

1. Using Information Schema and Conditional Logic:

This method uses the INFORMATION_SCHEMA.COLUMNS system view to get a list of columns in your table. Then, it employs dynamic SQL to build a query that checks for null values in each column.

Here's a breakdown:

• We declare variables for the table name and the dynamic SQL string.
• We loop through each column using a cursor or a loop construct (not shown here).
• Inside the loop, we build a conditional statement that checks if any rows in the table have a null value for the current column using EXISTS.
• If there are null values, the column name is set to null in the dynamic SQL string. Otherwise, the column name is added to the string.
• Finally, we execute the dynamic SQL to get a temporary result set containing only column names that potentially have null values.
• From this temporary result set, we can filter out columns with no null values using another query.

1. Using System DMVs and Temporary Table:

This approach leverages system Dynamic Management Views (DMVs) to get information about tables and columns. Here's the process:

• We create a temporary table to store results.
• We use a DMV query that joins tables like sys.schemas, sys.tables, and sys.columns to get details about tables and columns, including whether the column allows null values.
• We iterate through each table and column, checking if the column is nullable using the is_nullable property.
• We then use another query to count the total number of rows and non-null values in each column.
• By comparing the row count with the non-null count, we can identify columns that only contain null values.

Both methods have their advantages and disadvantages. The first approach is more flexible but can be less performant for large tables. The second approach is more efficient but requires manipulating system DMVs.

Important points to remember:

• These approaches identify columns with null values, not rows containing only nulls.
• Consider data analysis tools for a more comprehensive view of null values in your data.

DECLARE @tableName SYSNAME = 'YourTableName'; -- Replace with your table name
DECLARE @sql NVARCHAR(MAX) = '';

-- Loop through each column (not shown for brevity)
-- Assuming a cursor is used to iterate through columns

SET @sql = @sql + 
  CASE WHEN EXISTS (SELECT * FROM ' + @tableName + ' WHERE ' + c.name + ' IS NULL) THEN 'NULL'
       ELSE c.name + ', '
  END

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 2);

-- Build final query
SET @sql = 'SELECT ' + @sql + ' FROM ' + @tableName;

-- Execute dynamic SQL
EXEC sp_ExecuteSQL @sql;

-- Filter columns with no nulls (optional)
SELECT * FROM 
(
  -- Dynamic SQL result set
  SELECT * FROM EXEC sp_ExecuteSQL(@sql)
) AS Results
WHERE Results.[ColumnName] IS NOT NULL;

CREATE TABLE #Results (
  TableName VARCHAR(1000),
  ColumnName VARCHAR(1000),
  RowsCount INT,
  NonNullCount INT
);

DECLARE @schemaName SYSNAME = 'dbo'; -- Replace with your schema name (optional)

SELECT 
  S.[name] AS TableName,
  C.[name] AS ColumnName,
  COUNT(*) AS RowsCount,
  COUNT(CASE WHEN ' + C.[name] + ' IS NOT NULL THEN 1 END) AS NonNullCount
FROM ' + @schemaName + '.sys.Tables AS T
INNER JOIN ' + @schemaName + '.sys.Schemas AS S ON T.[schema_id] = S.[schema_id]
INNER JOIN ' + @schemaName + '.sys.Columns AS C ON OBJECT_ID(S.[name] + '.' + T.[name]) = C.[object_id]
WHERE C.is_nullable = 1
GROUP BY S.[name], C.[name];

SELECT TableName, ColumnName FROM #Results
WHERE RowsCount > 0 AND NonNullCount = 0;

DROP TABLE #Results;

1. INFORMATION_SCHEMA and Filtering:

This method leverages the INFORMATION_SCHEMA.COLUMNS view similar to the first approach, but instead of building dynamic SQL, it uses filtering within the main query.

SELECT c.name AS ColumnName
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE EXISTS (
  SELECT * FROM YourTable WHERE c.name IS NULL
)
AND c.is_nullable = 1;

This approach avoids dynamic SQL but might be less performant for very large tables.

1. System DMVs and Aggregation:

Similar to the second approach, we use system DMVs but with a slightly different logic.

DECLARE @schemaName SYSNAME = 'dbo'; -- Replace with your schema name (optional)

SELECT 
  C.[name] AS ColumnName
FROM ' + @schemaName + '.sys.Tables AS T
INNER JOIN ' + @schemaName + '.sys.Schemas AS S ON T.[schema_id] = S.[schema_id]
INNER JOIN ' + @schemaName + '.sys.Columns AS C ON OBJECT_ID(S.[name] + '.' + T.[name]) = C.[object_id]
WHERE C.is_nullable = 1
GROUP BY C.[name]
HAVING COUNT(*) = SUM(CASE WHEN ' + C.[name] + ' IS NOT NULL THEN 1 END);

This approach identifies columns with only null values by comparing the total row count with the non-null count for each column.

1. Data Profiling Tools:

Instead of relying on SQL queries, consider using data profiling tools offered by SQL Server Management Studio (SSMS) or third-party solutions. These tools can analyze tables and provide detailed statistics, including the percentage of null values in each column.

This method offers a more comprehensive view of null values and can be faster than complex SQL queries.

Choosing the right method depends on your specific needs.

• For a one-time analysis, a simple query using INFORMATION_SCHEMA might suffice.
• For regular monitoring or performance concerns, data profiling tools might be a better option.
• If you need to identify columns with null values for further processing within your SQL script, consider the first two approaches.