选择 SQL Server 中的所有空表 [英] Select all empty tables in SQL Server

问题描述

如何获取我的 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 2005 及更高版本上，您可以使用以下内容:

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

;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

CTE(Common Table Expression)中的内部选择计算每个表的行数并按表(OBJECT_ID)对它们进行分组，然后来自CTE的外部SELECT只抓取那些行(表)总行数为零.

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.

更新:如果你想检查非微软/系统表，你需要像这样扩展查询(加入sys.tables目录视图):

UPDATE: if you want to check for non-Microsoft / system tables, you need to extend the query like this (joining the sys.tables catalog view):

;WITH TableRows AS
(
   SELECT 
       SUM(ps.row_count) AS [RowCount], 
       t.Name AS TableName
   FROM 
       sys.dm_db_partition_stats ps
   INNER JOIN
       sys.tables t ON t.object_id = ps.object_id
   WHERE 
       (ps.index_id = 0 OR ps.index_id = 1)
       AND t.is_ms_shipped = 0
   GROUP BY 
       t.Name
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0

这篇关于选择 SQL Server 中的所有空表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！