sql - 查找没有数据的表

Question

在 Microsoft SQL Server 的情况下，我们如何检索数据库中没有数据的所有表(例如，表中没有行)？
有什么方法吗？

Answer 1

试试这个

   SELECT   TableName=OBJECT_NAME(OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats
   WHERE    index_id in (0 ,1)
   GROUP BY OBJECT_ID
   HAVING   SUM(row_count)  = 0

或者如果您只需要用户定义的表，则使用此

   SELECT TableName=OBJECT_NAME(s.OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats s
   JOIN     sys.tables  T
   ON       T.object_id = S.object_id       
   WHERE    index_id in (0 ,1)
   and      T.type  = 'U'
   GROUP BY s.OBJECT_ID
   HAVING   SUM(row_count)  = 0