Luouy~羽林
学问如逆水行舟,不进则退; 有知识的人不实践,等于一只蜜蜂不酿蜜; 我们可以由读书而收集知识,但必须利用思考把糠和谷子分开
--各数据表的空间使用量
CREATE VIEW DataBaseDestribute
AS
   
SELECT TOP 1000
    a3.name
AS [schemaname],
    a2.name
AS [tablename],
    a1.rows
as row_count,
    (a1.reserved
+ ISNULL(a4.reserved,0))* 8 AS [reserved(K)],
    a1.data
* 8 AS [data(k)],
    (
CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size(k)],
    (
CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused(k)],
    a1.data
* 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow
   
FROM
        (
       
SELECT
        ps.
object_id,
       
SUM (
       
CASE
       
WHEN (ps.index_id < 2) THEN row_count
       
ELSE 0
       
END
        )
AS [rows],
       
SUM (ps.reserved_page_count) AS reserved,
       
SUM (
       
CASE
       
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
       
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
       
END
        )
AS data,
       
SUM (ps.used_page_count) AS used
       
FROM sys.dm_db_partition_stats ps
       
GROUP BY ps.object_id) AS a1
       
LEFT OUTER JOIN
            (
           
SELECT
            it.parent_id,
           
SUM(ps.reserved_page_count) AS reserved,
           
SUM(ps.used_page_count) AS used
           
FROM sys.dm_db_partition_stats ps
           
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
           
WHERE it.internal_type IN (202,204)
           
GROUP BY it.parent_id
            )
AS a4 ON (a4.parent_id = a1.object_id)
   
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
   
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
   
WHERE a2.type <> N'S' and a2.type <> N'IT'
   
ORDER BY [reserved(K)] DESC
GO

SELECT * FROM DataBaseDestribute
posted on 2011-10-09 09:43  羽林.Luouy  阅读(929)  评论(0编辑  收藏  举报