Find tables without data

How can we retrieve all tables in database without data (as in, there are no rows in table) in the case of a Microsoft SQL Server?
Is there any method?

---

Try this

   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

OR If u need only user defined tables then use this

   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

---

尝试这个 -

WITH CTE AS
(
SELECT   sc.name +'.'+ ta.name TableName
         ,SUM(pa.rows) RowCnt
FROM     sys.tables ta
         INNER JOIN sys.partitions pa
                    ON pa.OBJECT_ID = ta.OBJECT_ID
         INNER JOIN sys.schemas sc
                    ON ta.schema_id = sc.schema_id
WHERE    ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
)

SELECT * FROM CTE WHERE RowCnt = 0

---

To get the list of empty tables, we can use the below 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 –

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

链接地址: http://www.djcxy.com/p/21466.html

上一篇: 如何禁用Tkinter中的Combobox？

下一篇: 找到没有数据的表格