SQL Server Management Studio - 查找所有非空表

Question

有没有一种方法可以在SQL Server Management Studio Express中列出所有非空表格？我有超过100个表要查看并检查数据。

- Peter Sun

---

在一个数据库还是多个数据库中？ - JNK

Answer 1

你可以尝试使用 sysindexes 和 INFORMATION_SCHEMA.TABLES :)

SELECT 'Table Name'=convert(char(25),t.TABLE_NAME),
      'Total Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
HAVING max(i.rows)>0
ORDER BY 'Total Record Count' DESC

- Morris Miao

---

我最终使用了这段代码，因为我不知道自己在做什么... 我只是添加了databasename.INFORM... - Peter Sun

我喜欢你在表格中包含少于0行的方式 ;) - Josh Noe

@JoshNoe，我修好了。真不敢相信它这么久都没被发现。 - Igor Popov

Answer 2

Morris Miao的解决方案 使用已弃用的 sys.sysindexes 视图，并基于表名执行与 INFORMATION_SCHEMA.TABLES 的连接，但不能保证唯一性, 即使在同一个数据库中也不行。

Simon的解决方案 没有限定到当前数据库，但可以通过使用sys.tables进行改进：

SELECT r.table_name, r.row_count, r.[object_id]
FROM sys.tables t
INNER JOIN (
    SELECT OBJECT_NAME(s.[object_id]) table_name, SUM(s.row_count) row_count, s.[object_id]
    FROM sys.dm_db_partition_stats s
    WHERE s.index_id in (0,1)
    GROUP BY s.[object_id]
) r on t.[object_id] = r.[object_id]
WHERE r.row_count > 0
ORDER BY r.table_name;

- Mankarse

---

除非用户是 Server Admin 用户，否则这种方法无法工作。这将导致在数据库 '<db name>' 中拒绝“VIEW DATABASE STATE”权限。 - Kim Crosser

Answer 3

尝试：

WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      index_id = 0 OR index_id = 1
   GROUP BY 
      OBJECT_ID
)

SELECT *
FROM TableRows
WHERE [RowCount] > 0

- Simon

---

如何获取一个数据库中非空的表？ - Peter Sun

@PeterSun：请见我的回答。 - Mankarse

Answer 4

假设表可以分为两种类型：

1. 聚集表（具有聚集索引的表）
2. 堆表（没有聚集索引的表）

在SQL Server中，所有表都被划分为分区。因此每个表至少会有一个分区。

在 sys.partitions 中，每个表的每个分区都存在一行。 sys.partitions 中的这些行包含相应表的该分区中的行数量信息。

由于SQL Server中的所有表都包含至少一个分区，因此我们可以从 sys.partitions 获取关于表中行数的信息。

SELECT
        OBJECT_NAME(T.OBJECT_ID) AS TABLE_NAME,
        SUM(P.ROWS)  AS TOTAL_ROWS
FROM
        SYS.TABLES T
INNER JOIN 
        SYS.PARTITIONS P 
        ON T.OBJECT_ID = P.OBJECT_ID
WHERE 
        P.INDEX_ID IN (0,1)
GROUP BY 
        T.OBJECT_ID
HAVING 
        SUM(P.ROWS) > 0

当我们在不同的分区中对行求和时，考虑到index_id(0,1)

• index_id = 0表示堆叠
• index_id = 1表示聚集索引
• index_id > 1表示非聚集索引。

一张表只能有一个聚集索引或没有。

但是对于非聚集索引，情况并非如此。一张表可以有多个非聚集索引。 因此，在求和行时不能使用这些index_id。

• 堆叠表将具有index_id = 0
• 聚集表将具有index_id = 1

- jophab

---

这是唯一一个可以在不需要用户成为服务器管理员（或在数据库上明确授予VIEW DATABASE STATE特权）的情况下运行的查询。 - Kim Crosser

Answer 5

这是对@jophab's answer的轻微改进。它将显示表的架构，并使用sys.partitions比较rows = 0。

SELECT 
    sch.name as SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.OBJECT_ID 
INNER JOIN sys.schemas sch
    on t.schema_id = sch.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND p.rows = 0
GROUP BY 
    sch.name,t.Name, p.Rows
ORDER BY 
    sch.name,t.Name

- jitin14

Answer 6

你也可以使用“对象资源管理器详细信息（F7）”，导航到感兴趣的数据库的“表”文件夹，并设置对象资源管理器详细信息以显示行数（右键单击标题）

- THW