SQL Server: Number of disk blocks used by index or table

In SQL Server, how can I see th开发者_如何学运维e number of disk blocks an index or a tables uses?

Using this query, you can determine the number of pages (8 KB blocks of space) a SQL Server table uses:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalSpace, 
    SUM(a.used_pages) AS UsedSpace
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 = 'YourTableNameHere'
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

This sums up all the data and index pages a table uses.

This is the best one I have used up to now. Taken from here

set nocount on
--dbcc updateusage(0) with count_rows

declare @tables table (
   sizerank int identity (1, 1) not null
 , table_name varchar (255) null
 , table_total_size_dec decimal(18, 1) null
 , table_size_measure varchar(10) null
)

insert into @tables (table_name, table_total_size_dec, table_size_measure)
select upper(name) as [table_name]
     , convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) as sizedec
     , right(sizemb, 2) as measure
from      (select convert(varchar(255), so.name) as name
                , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                            then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1))) + 'mb'
                       else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0))) + 'kb' 
                       end as sizemb
           from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid
           where objectproperty(so.id, 'IsUserTable') = 1
           group by so.name) as dv
order by right(sizemb, 2) desc, convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) desc

select t.*, indname, indid, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) as indsizedec, right(indsizemb, 2) as indmeasure
from @tables t
left join (
select convert(varchar(255), so.name) as tblname
     , convert(varchar(255), si.name) as indname
     , si.indid
     , case when sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024 > 1000 
                 then convert(varchar(255), convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1))) + 'mb' 
            else convert(varchar(255), convert(int, round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1024), 0))) + 'kb' 
            end as indsizemb
from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid 
where si.dpages > 0
group by so.name, si.name, si.indid
) as dvtablesindexes on t.table_name = dvtablesindexes.tblname
where table_total_size_dec > 0
order by sizerank, right(indsizemb, 2) desc, convert(decimal(15, 1), left(indsizemb, len(indsizemb)-2)) desc