SQL Server 中的表和索引大小 [英] Table and Index size in SQL Server

问题描述

我们能否有一个 SQL 查询，它基本上有助于查看 SQL Server 中的表和索引大小.

Can we have a SQL query which will basically help in viewing table and index sizes in SQl Server.

SQL Server 如何维护表/索引的内存使用情况?

How SQL server maintains memory usage for tables/indexes?

推荐答案

不带参数的 exec sp_spaceused 显示整个数据库的摘要.foreachtable 解决方案为每个表生成一个结果集 - 如果您有太多表，SSMS 可能无法处理.

The exec sp_spaceused without parameter shows the summary for the whole database. The foreachtable solution generates one result set per table - which SSMS might not be able to handle if you have too many tables.

我创建了一个 脚本 通过 sp_spaceused 收集表信息并在单个记录集中显示摘要，按大小排序.

I created a script which collects the table infos via sp_spaceused and displays a summary in a single record set, sorted by size.

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

这篇关于SQL Server 中的表和索引大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！