--查看数据库大小

SELECT DB_NAME(database_id) AS [Database Name],

[Name] AS [Logical Name],

[Physical_Name] AS [Physical Name],

((size * 8) / 1024) AS [Size(MB)]

FROM sys.master_files

ORDER BY [Size(MB)] DESC

--查看数据库索引大小

USE ldtcasedossier

GO

SELECT tn.[name] AS [Table name], ix.[name] AS [Index name],

SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]

FROM sys.dm_db_partition_stats AS sz

INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id]

AND sz.[index_id] = ix.[index_id]

INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id

GROUP BY tn.[name], ix.[name]

ORDER BY tn.[name]

---查询数据库表

--第一种

EXEC sp_spaceused '表_测试表';

--第二种

SELECT 

    OBJECT_NAME(object_id) AS TableName,

    SUM(used_page_count) * 8 AS UsedSpaceKB

FROM 

    sys.dm_db_partition_stats

GROUP BY 

    object_id;

--查询数据库所有表占用

SELECT 

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

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

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.NAME NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255 

GROUP BY 

    t.Name, s.Name, p.Rows

ORDER BY 

    TotalSpaceKB DESC;