时间戳

首页 > 解决方案 > 查找 pk 为 int（不是 bigint）的最大表

sql-server - 查找 pk 为 int（不是 bigint）的最大表

问题描述

我想找到行数最多的表，并排除带有 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

标签： sql-servertsql

解决方案

您可以使用 a NOT EXISTSin 并对主键列的对象进行WHERE横向连接。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;

推荐阅读