如何查找 SQLServer 数据库中的最大对象？

我一直在使用这个 SQL 脚本(我从某个地方得到的——无法重建它来自谁) ，它帮助我相当多地理解和确定索引和表的大小:

SELECT
t.name AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE
t.name NOT LIKE 'dt%' AND
i.object_id > 255 AND
i.index_id <= 1
GROUP BY
t.name, i.object_id, i.index_id, i.name
ORDER BY
object_name(i.object_id)

当然，您可以使用其他订购条件，例如。

ORDER BY SUM(p.rows) DESC

以获得行数最多的表，或者

ORDER BY SUM(a.total_pages) DESC

以获得使用页面最多(8K 块)的表。

如果使用的是 SqlServerManagementStudio2008，则可以在对象资源管理器详细信息窗口中查看某些数据字段。只需浏览并选择 table 文件夹。在详细信息视图中，您可以右键单击列标题并将字段添加到“报告”中。如果您使用 SSMS2008特快，您的里程数可能会有所不同。

你亦可使用以下代码:

USE AdventureWork
GO
CREATE TABLE #GetLargest
(
table_name    sysname ,
row_count     INT,
reserved_size VARCHAR(50),
data_size     VARCHAR(50),
index_size    VARCHAR(50),
unused_size   VARCHAR(50)
)


SET NOCOUNT ON


INSERT #GetLargest


EXEC sp_msforeachtable 'sp_spaceused ''?'''


SELECT
a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #GetLargest a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC


DROP TABLE #GetLargest

我发现这个查询在 SqlServerCentral 中也非常有用，下面是原始文章的链接

SqlServer 最大的表

  select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*sum( case
when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
else lob_used_page_count + row_overflow_used_page_count
end )
, index_kb=8*(sum(used_page_count)
- sum( case
when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
else lob_used_page_count + row_overflow_used_page_count
end )
)
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by
rows desc

在我的数据库中，他们在这个查询和第一个答案之间给出了不同的结果。

希望有人能帮上忙

此查询有助于查找您所连接的最大表。

SELECT  TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

在 SQLServer2008中，您还可以根据主表运行标准报表磁盘使用率。这可以通过 右键点击数据库找到，选择 Reports-> Standard Reports 并选择您想要的报告。

@ marc _ s 的回答非常棒，我已经用了好几年了。但是，我注意到该脚本忽略了一些列存储索引中的数据，并且没有显示完整的图片。例如，当您对脚本执行 SUM(TotalSpace)操作并将其与 ManagementStudio 中的总空间数据库属性进行比较时，在我的例子中数字不匹配(ManagementStudio 显示更大的数字)。我修改了脚本来克服这个问题，并对它进行了一些扩展:

select
tables.[name] as table_name,
schemas.[name] as schema_name,
isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') as database_name,
sum(allocation_units.total_pages) * 8 as total_space_kb,
cast(round(((sum(allocation_units.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as total_space_mb,
sum(allocation_units.used_pages) * 8 as used_space_kb,
cast(round(((sum(allocation_units.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as used_space_mb,
(sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8 as unused_space_kb,
cast(round(((sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unused_space_mb,
count(distinct indexes.index_id) as indexes_count,
max(dm_db_partition_stats.row_count) as row_count,
iif(max(isnull(user_seeks, 0)) = 0 and max(isnull(user_scans, 0)) = 0 and max(isnull(user_lookups, 0)) = 0, 1, 0) as no_reads,
iif(max(isnull(user_updates, 0)) = 0, 1, 0) as no_writes,
max(isnull(user_seeks, 0)) as user_seeks,
max(isnull(user_scans, 0)) as user_scans,
max(isnull(user_lookups, 0)) as user_lookups,
max(isnull(user_updates, 0)) as user_updates,
max(last_user_seek) as last_user_seek,
max(last_user_scan) as last_user_scan,
max(last_user_lookup) as last_user_lookup,
max(last_user_update) as last_user_update,
max(tables.create_date) as create_date,
max(tables.modify_date) as modify_date
from
sys.tables
left join sys.schemas on schemas.schema_id = tables.schema_id
left join sys.indexes on tables.object_id = indexes.object_id
left join sys.partitions on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id
left join sys.allocation_units on partitions.partition_id = allocation_units.container_id
left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id and indexes.index_id = dm_db_index_usage_stats.index_id
left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.object_id and indexes.index_id = dm_db_partition_stats.index_id
group by schemas.[name], tables.[name], isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown')
order by 5 desc

希望对某些人有所帮助。 该脚本针对大型 TB 范围的数据库进行了测试，这些数据库具有数百个不同的表、索引和模式。