查詢Database、Table、Index的Size

--查詢資料庫大小
SELECT 
DB_NAME(database_id) N'資料庫',
physical_name N'實體檔案',
type_desc N'檔案類型',
state_desc N'檔案狀態',
round(size*8.0/1024,0) N'檔案大小(MB)'
FROM sys.master_files
order by 5 desc

--查詢Table大小

CREATE TABLE #TableSizes 
( 
table_name SYSNAME, 
row_count int, 
reserved_size varchar(10), 
data_size varchar(10), 
index_size varchar(10), 
unused_size varchar(10) 
) 
INSERT #TableSizes EXEC sp_MSforeachtable 'sp_spaceused ''?''' 
select * from (
SELECT top 100 table_name,row_count,
replace(reserved_size,' KB','') reserved_size,
replace(data_size,' KB','') data_size,
replace(index_size,' KB','') index_size,
replace(unused_size,' KB','') unused_size
FROM #TableSizes
where 1=1
order by 2 desc) a
order by 1
drop table #TableSizes 
go



--查詢Index小大
SELECT t.table_name as schema_table, t.index_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
, sum(t.tbl_rows) as rows
from
(
SELECT s.Name schema_name
, o.Name table_name
, coalesce(i.Name, 'HEAP') index_name
, p.used_page_count * 8 used
, p.reserved_page_count * 8 reserved
, p.row_count ind_rows
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end tbl_rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o
ON o.object_id = p.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
) as t
GROUP BY t.schema_name, t.table_name, t.index_name
ORDER BY 2