选择SQL Server中的所有空表

How to get the list of the tables in my sql-server database that do not have any records in them?

如何获取我的sql-server数据库中没有任何记录的表列表？

5 个解决方案

#1

On SQL Server 2005 and up, you can use something like this:

在SQL Server 2005及更高版本上，您可以使用以下内容：

;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

The inner select in the CTE (Common Table Expression) calculates the number of rows for each table and groups them by table (OBJECT_ID), and the outer SELECT from the CTE then grabs only those rows (tables) which have a total number of rows equal to zero.

CTE中的内部选择（公用表表达式）计算每个表的行数并按表（OBJECT_ID）对它们进行分组，然后CTE中的外部SELECT只捕获具有总行数的行（表）等于零。

#2

To get the list of empty tables, we can use the below tsql –

要获取空表的列表，我们可以使用下面的tsql -

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

And, to get a list of tables having at least one row of data, we can use the below tsql –

并且，要获得至少有一行数据的表列表，我们可以使用下面的tsql -

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Note: List of table included only 'User Table' i.e. Not included 'System Table'.

注意：表格列表仅包括“用户表”，即不包括“系统表”。

#3

select a.rows as Rowcnt,
   b.name as Tbl_Name 
from sys.partitions a
join sys.tables b
   on a.object_id=b.object_id
where b.type='u' 
   and a.rows = 0

#4

  SELECT name AS [TableList] FROM SYS.DM_DB_PARTITION_STATS s 
  INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
  WHERE row_count = 0

#5

We can simply classify Tables into two types.

我们可以简单地将表分类为两种类型。

Clustered Table ( Tables having a Clustered Index )
聚类表（具有聚簇索引的表）
Heap Tables ( Tables not having a Clustered Index )
堆表（没有聚簇索引的表）

In SQL Server, all tables are divided into partitions. There will be at least one partition for each table .

在SQL Server中，所有表都分为多个分区。每个表至少有一个分区。

In sys.partitions, there exists one row for each partition of all tables.

在sys.partitions中，所有表的每个分区都存在一行。

The entries in sys.partitions contains a column for number of rows in that partition of the corresponding table.

sys.partitions中的条目包含相应表的该分区中的行数列。

Since all tables in SQL Server contain aleast one partition, we can get the information about number of rows in a table from 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

While taking sum of rows in different partitions, we are considering index_id (0,1)

在获取不同分区中的行总数时，我们正在考虑index_id（0,1）

index_id = 0 for Heap
对于Heap，index_id = 0
index_id = 1 for Clustered index
对于聚簇索引，index_id = 1
index_id > 1 are for nonclustered index.
index_id> 1用于非聚簇索引。

A table can have either one clustered index or none.

一个表可以有一个聚簇索引，也可以没有。

But a table can have multiple nonclustered indexes. So we cannot use index_ids of nonclustered indexes while summing rows.

但是表可以有多个非聚簇索引。因此，在对行进行求和时，我们不能使用非聚簇索引的index_ids。

Heap Tables will be having index_id = 0
堆表将具有index_id = 0
Clustered Tables will be having index_id = 1
集群表将具有index_id = 1

#1