查找pk为int(不是bigint)的最大表

Question

我想找到行数最多的表，排除主键为bigint的表。

下面的查询运行得非常快，似乎工作，但我如何修改它来过滤掉bigint表?我也许可以忽略大于MAX_INT的row_counts，但如果一个表是bigint，但还没有很多行，那就行不通了。

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  
GROUP BY    O.schema_id, O.Name
order by Total_RowCount desc

Answer 1

您可以在WHERE中使用NOT EXISTS，并对主键列的sys.types对象进行横向连接。我假设如果一个PK由多列组成，其中一个是bigint，您想忽略它:

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
AND NOT EXISTS (SELECT 1
FROM sys.indexes i 
JOIN sys.index_columns ic ON o.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON o.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.types ct ON c.system_type_id = ct.system_type_id
WHERE o.object_id = i.object_id
AND i.is_primary_key = 1
AND ct.[name] = 'bigint')
GROUP BY o.schema_id,
o.name
ORDER BY Total_RowCount DESC;