统计数据库空间的使用情况sql脚本

with  pa as
(
SELECT p.object_id,p.index_id,a.type_desc as pagetype_desc,a.total_pages,a.used_pages,a.data_pages
FROM sys.partitions p JOIN sys.allocation_units a
   ON p.partition_id = a.container_id
),
indexes as
(
    select object_id,index_id,object_name(object_id) as tbname , name as indexname,type_desc as tbtype_desc
    from sys.indexes
    where object_id > =100
),
result as
(
select i.*,p.pagetype_desc,p.total_pages,p.used_pages,p.data_pages
from pa p inner join indexes i
on p.object_id=i.object_id and p.index_id=i.index_id
)

select  * from result  order by total_pages desc  

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc  

select b.name,a.* from sys.dm_db_index_usage_stats a inner join sysindexes b on (a.object_id = b.id) order by a.user_seeks desc