如何修改SQL查询，筛选行数最多且主键为int非bigint的表

解决方案 #

我们可以通过NOT EXISTS子查询关联系统视图，过滤掉主键列数据类型为bigint的表，不会影响原有查询的性能和统计逻辑。

修改后的完整SQL：

SELECT      
    SCHEMA_NAME(O.schema_id) + '.' + O.Name As [Schema.TableName], 
    SUM(P.rows) AS 'Total_RowCount'
FROM        
    sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE     
    O.type = 'U'
    AND P.index_id < 2  
    -- 过滤主键为bigint类型的表
    AND NOT EXISTS (
        SELECT 1
        FROM sys.indexes pk_idx
        INNER JOIN sys.index_columns pk_col 
            ON pk_idx.object_id = pk_col.object_id AND pk_idx.index_id = pk_col.index_id
        INNER JOIN sys.columns c 
            ON pk_col.object_id = c.object_id AND pk_col.column_id = c.column_id
        INNER JOIN sys.types t 
            ON c.system_type_id = t.system_type_id
        WHERE 
            pk_idx.object_id = O.object_id
            AND pk_idx.is_primary_key = 1
            AND t.name = 'bigint'
    )
GROUP BY    
    O.schema_id, O.Name
ORDER BY Total_RowCount DESC

逻辑说明：

• 无主键的表会正常保留，不会被误过滤
• 仅根据主键的字段类型过滤，和表当前存储的行数无关，覆盖了bigint主键表行数较少的场景
• 所有关联的都是系统内置视图，查询效率高，不会明显提升原有SQL的耗时

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