SQL查看磁盘空间与数据库文件使用空间

--EXEC GET_DEVICEMESSAGE--必须要有XP_CMDSHELL的执行权限
--查看本要所有磁盘分区的总大小，剩余空间(我试了几台电脑.有时候有些磁盘分区没显示大小出来)
ALTER PROC GET_DEVICEMESSAGE
AS
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL




DROP TABLE #T
IF OBJECT_ID('TEMPDB..#T1') IS NOT NULL
DROP TABLE #T1

SELECT 
 ID=IDENTITY(INT,1,1),CONVERT(NVARCHAR(4000),' ') AS B  INTO #T
FROM 
 MASTER..SPT_VALUES 
WHERE 
 1=2
--SELECT * FROM #T
INSERT #T EXEC MASTER..XP_CMDSHELL 'WMIC LOGICALDISK LIST BRIEF'
--以上如果能直接以表格式输出结果集就更方便了(求CMD命令指导）
--还有一种法也可以利用自动测试脚本生成字符串再动态执行产生临时表（后面的语句就可以省略若干）
--http://topic.csdn.net/u/20080516/15/3fcf4880-67e9-4a28-844d-05985db51215.html

SELECT 
 *,
 SUBSTRING(B,1,CHARINDEX(':',B)) AS 盘符,
 REPLACE(STUFF(B,1,CHARINDEX(':',B),''),' ','.') C INTO #T1
FROM 
 #T 
WHERE 
 ID BETWEEN 2 AND (SELECT MAX(ID) FROM #T WHERE B IS NOT NULL)

WHILE EXISTS(SELECT 1 FROM #T1 WHERE C LIKE '%..%')
BEGIN
 UPDATE #T1 SET C=REPLACE(C,'..','.') WHERE C LIKE '%..%'
END

UPDATE #T1 SET C=REPLACE(C,CHAR(13),'')
UPDATE #T1 SET C=LEFT(RIGHT(C,LEN(C)-1),LEN(RIGHT(C,LEN(C)-1))-1) WHERE LEN(C)>1
UPDATE #T1 SET C=SUBSTRING(C,1,PATINDEX('%[^0-9,.]%',C)-2) WHERE C LIKE '%[^0-9,.]%'

--SELECT * FROM #T1  WHERE C LIKE '%[^0-9,.]%'
SELECT 
 CONVERT(NVARCHAR(20),RTRIM(盘符)) DEVICEID,
 CONVERT(DEC(18,2),CONVERT(BIGINT,PARSENAME(C,1))/POWER(1024.0,3)) AS [SIZE(GB)],
 CONVERT(DEC(18,2),CONVERT(BIGINT,PARSENAME(C,2))/POWER(1024.0,3)) [FREESPACE(GB)] INTO ##T2
FROM 
 #T1 WHERE C LIKE '%.%'

DROP TABLE #T,#T1

GO

--EXEC GET_DBSYSFILES
--查找本机所有实例数据库
ALTER PROC GET_DBSYSFILES
AS
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..##SYSFILES') IS NOT NULL
DROP TABLE ##SYSFILES
SELECT CONVERT(NVARCHAR(128),' ') AS SRVNETNAME, * INTO ##SYSFILES FROM MASTER..SYSFILES WHERE 1=2


DECLARE CUR_FILE CURSOR
FOR
SELECT DISTINCT SRVNETNAME FROM SYSSERVERS WHERE ISREMOTE=0

DECLARE @SRVNETNAME SYSNAME,@SQL NVARCHAR(4000)
OPEN CUR_FILE
FETCH NEXT FROM CUR_FILE INTO @SRVNETNAME
WHILE @@FETCH_STATUS=0
BEGIN
 SET @SQL='SELECT '+RTRIM(@SRVNETNAME)+',* FROM ['+RTRIM(@SRVNETNAME)+'].[?].DBO.SYSFILES'
 --以上需要建立链接服务器,才能把本机所有运行的实例所用的数据文件大小找出来
 INSERT ##SYSFILES EXEC SP_MSFOREACHDB @SQL
 SET @SQL=''
 FETCH NEXT FROM CUR_FILE INTO @SRVNETNAME
END
CLOSE CUR_FILE
DEALLOCATE CUR_FILE

--SELECT * FROM ##SYSFILES
--DROP TABLE ##SYSFILES

----------------------------------------------------华丽的分割线----------------------------------------------
--1.查看磁盘分区总大小。剩余空间。数据库文件使用大小
SELECT
 DEV.DEVICEID,DEV.[SIZE(GB)] AS DEVSIZE,DEV.[FREESPACE(GB)] AS DEVFREE
 DB.[SIZE(GB)] AS DBSIZE
FROM
(
 SELECT 
  SUBSTRING(FILENAME,1,CHARINDEX(':',FILENAME)) AS DEVICEID,
  SUM(SIZE*8.0/(1024.0*1024.0)) AS [SIZE(GB)]
 FROM
  ##SYSFILES
 GROUP BY
  SUBSTRING(FILENAME,1,CHARINDEX(':',FILENAME)) 
)DB,##T2  AS DEV
WHERE
 DB.DEVICEID=DEV.DEVICEID
--2.查看所有实例数据库文件的最大的前10,有兴趣的可以改成查看每个实例文件最大的前10等等
SELECT
 TOP 10 *
FROM
 ##SYSFILES

ORDER BY SIZE DESC

--此处省略N万字.