如何修复SQL查询中计算索引列NULL值占比的子查询报错问题

Got it, let's break down why that subquery is failing and fix it properly.

Why Your Original Subquery Breaks #

The commented line throws an error because FROM t.name treats t.name as a literal string, not a reference to the actual table in your outer query. SQL Server tries to find a table named exactly the string value of t.name (e.g., if your table is Users, it looks for a table called 'Users'—which isn't valid), hence the "Invalid object name" message.

To calculate the NULL percentage for each indexed column, we need to dynamically reference the real table and column from the system catalogs. Here are two reliable solutions:

Solution 1: Use OUTER APPLY with Dynamic SQL (Per-Row Execution) #

This approach runs a small dynamic query for each indexed column to calculate its NULL percentage. It's straightforward but may have performance overhead if you have hundreds of tables/indexes.

SELECT 
    TableName = t.name, 
    IndexName = ind.name, 
    IndexId = ind.index_id, 
    ColumnId = ic.index_column_id, 
    ColumnName = col.name, 
    nulls_percent = stats.nulls_percent,
    ind.*, 
    ic.*, 
    col.* 
FROM sys.indexes ind 
INNER JOIN sys.index_columns ic 
    ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id 
INNER JOIN sys.columns col 
    ON ic.object_id = col.object_id AND ic.column_id = col.column_id 
INNER JOIN sys.tables t 
    ON ind.object_id = t.object_id
OUTER APPLY (
    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT (COUNT_BIG(*) - COUNT_BIG([' + QUOTENAME(col.name) + N'])) * 100.0 / COUNT_BIG(*) AS nulls_percent
        FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N'
    '
    DECLARE @nulls_percent DECIMAL(5,2)
    EXEC sp_executesql @sql, N'@nulls_percent DECIMAL(5,2) OUTPUT', @nulls_percent OUTPUT
    SELECT @nulls_percent AS nulls_percent
) AS stats
WHERE 
    ind.is_primary_key = 0 
    AND ind.is_unique = 0 
    AND ind.is_unique_constraint = 0 
    AND t.is_ms_shipped = 0 
ORDER BY 
    t.name, 
    ind.name, 
    ind.index_id, 
    ic.is_included_column, 
    ic.key_ordinal;

Key notes here:

• QUOTENAME handles special characters/reserved words in table/column names (e.g., [Order Details]).
• OBJECT_SCHEMA_NAME ensures we reference the correct schema for the table.
• COUNT_BIG prevents integer overflow with large datasets.

Solution 2: Generate a Single Dynamic Query (Batch Execution) #

This method builds one large dynamic query that calculates all NULL percentages in a single execution. It's far more efficient for large numbers of tables/indexes.

DECLARE @sql NVARCHAR(MAX) = N''

-- Build the base query for each indexed column
SELECT @sql += N'
UNION ALL
SELECT 
    TableName = ''' + QUOTENAME(t.name) + N''',
    IndexName = ''' + QUOTENAME(ind.name) + N''',
    IndexId = ' + CAST(ind.index_id AS NVARCHAR(10)) + N',
    ColumnId = ' + CAST(ic.index_column_id AS NVARCHAR(10)) + N',
    ColumnName = ''' + QUOTENAME(col.name) + N''',
    nulls_percent = (COUNT_BIG(*) - COUNT_BIG([' + QUOTENAME(col.name) + N'])) * 100.0 / COUNT_BIG(*),
    ind.*, 
    ic.*, 
    col.*
FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N'
CROSS JOIN (SELECT * FROM sys.indexes WHERE object_id = ' + CAST(t.object_id AS NVARCHAR(20)) + N' AND index_id = ' + CAST(ind.index_id AS NVARCHAR(10)) + N') AS ind
CROSS JOIN (SELECT * FROM sys.index_columns WHERE object_id = ' + CAST(t.object_id AS NVARCHAR(20)) + N' AND index_id = ' + CAST(ind.index_id AS NVARCHAR(10)) + N' AND column_id = ' + CAST(col.column_id AS NVARCHAR(10)) + N') AS ic
CROSS JOIN (SELECT * FROM sys.columns WHERE object_id = ' + CAST(t.object_id AS NVARCHAR(20)) + N' AND column_id = ' + CAST(col.column_id AS NVARCHAR(10)) + N') AS col
WHERE 
    ind.is_primary_key = 0 
    AND ind.is_unique = 0 
    AND ind.is_unique_constraint = 0 
    AND t.is_ms_shipped = 0'
FROM sys.indexes ind 
INNER JOIN sys.index_columns ic 
    ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id 
INNER JOIN sys.columns col 
    ON ic.object_id = col.object_id AND ic.column_id = col.column_id 
INNER JOIN sys.tables t 
    ON ind.object_id = t.object_id
WHERE 
    ind.is_primary_key = 0 
    AND ind.is_unique = 0 
    AND ind.is_unique_constraint = 0 
    AND t.is_ms_shipped = 0

-- Remove the leading UNION ALL
SET @sql = STUFF(@sql, 1, 10, N'')

-- Add final sorting
SET @sql += N'
ORDER BY 
    TableName, 
    IndexName, 
    IndexId, 
    ic.is_included_column, 
    ic.key_ordinal'

-- Execute the dynamic query
EXEC sp_executesql @sql

Which Solution to Choose? #

• Use Solution 1 if you have a small number of tables/indexes and prefer readability over raw performance.
• Use Solution 2 for large databases with many tables/indexes—it minimizes round-trips to the database engine.

内容的提问来源于stack exchange，提问作者Francesco Mantovani