找到没有数据的表格
在Microsoft SQL Server的情况下，我们如何检索数据库中没有数据的所有表（如表中没有行）？
有什么方法吗？
尝试这个
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
尝试这个 -
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
要获得空表的列表，我们可以使用下面的tsql -
EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '
而且，要获得至少有一行数据的表的列表，我们可以使用下面的tsql -
EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '