如何在sql server 2008R2中找到所有空表 [英] How to find all empty table in sql server 2008R2

问题描述

亲爱的所有人，



有一种非常简单的方法可以为它编写查询。



SELECT

t.name table_name，

s.name SCHEMA_NAME，

sum（p.rows）total_rows

FROM

sys.tables t

加入sys.schemas s（t.schema_id = s.schema_id）

加入sys.partitions p on（ t.object_id = p.object_id）

WHERE p.index_id in（0,1）

GROUP BY t.name，s.name

具有总和（p.rows）= 0

Dear All,

There is very simple way to write query for same.

SELECT
t.name table_name,
s.name SCHEMA_NAME,
sum(p.rows) total_rows
FROM
sys.tables t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
WHERE p.index_id in (0,1)
GROUP BY t.name,s.name
HAVING sum(p.rows) = 0

推荐答案

此查询不仅对于空表非常有用，而且还可以概述完全消耗的空间以及在哪里：

This query is veryusefull not only for empty tables but also to get an overview of the space consumed totally and where:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
	
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

这篇关于如何在sql server 2008R2中找到所有空表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！