常用脚本--查看数据库文件大小

--============================================================================
--查看数据库文件大小
SELECT 
DB_NAME(F.database_id) AS DBName,
F.name AS LogicName,
F.size*8/1024 AS SizeMB
FROM sys.master_files F
INNER JOIN sys.databases D
ON D.database_id=F.database_id
INNER JOIN sys.database_mirroring dm 
on D.database_id=dm.database_id
AND (dm.mirroring_guid IS NULL OR dm.mirroring_role=1)
WHERE F.database_id>4
ORDER BY SizeMB DESC
--============================================================================

 

--============================================================================
--查看当前实例下各数据库数据文件中可收缩情况
--UnusedExtents 标示可以被shrink的分区数

DROP TABLE #T
GO 
DROP TABLE #T1
GO
CREATE  TABLE #T
(
    DatabaseID INT,
    FileID INT,
    FileGroup INT,
    TotalExtents INT,
    UsedExtents INT,
    LogicName NVARCHAR(200),
    FilePath NVARCHAR(500)
)

CREATE  TABLE #T1
(
    FileID INT,
    FileGroup INT,
    TotalExtents INT,
    UsedExtents INT,
    LogicName NVARCHAR(200),
    FilePath NVARCHAR(500)
)

EXEC sp_MSforeachdb N'
USE [?]
DELETE FROM  #T1
INSERT INTO  #T1(FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath)
EXEC(''DBCC SHOWFILESTATS'')

INSERT INTO  #T(DatabaseID,FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath)
SELECT DB_ID(),FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath FROM #T1
'

SELECT DB_NAME(T.DatabaseID) AS DatabaseName,
(T.TotalExtents-T.UsedExtents) AS UnusedExtents,
* FROM #T AS T
ORDER BY UnusedExtents DESC
--============================================================================