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

[SQL77]

--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万字.

---------欢迎各位大虾指导,拍砖---最重要的是扩展------------------------------------------------------------------------

[天才在左疯子在右]

很多都有一些漏洞

[寻道模式]

代码写的格式很漂亮，这样的sql也很有用

[lanmenglingyun]

不错，学习下

[l4kangaxx]

佩服佩服，lz果然可以的

[suyuan1111]

很多都有一些漏洞

[SQL77]

[Quote=引用 46 楼 的回复:]
很多都有一些漏洞
[/Quote]
欢迎指正。

[miao123777]

不懂啊 怎么弄的啊？》

[lao_bulls]

多谢分享啊

[wjw220]

多谢分享xiexie

[hg2980986]

[chen7788]

好像很腻害的样纸！

[缘来是你you]

高深莫测

[微信公众号]

都是大牛啊……

[ke280563116]

有点难

[winteredchengxu]

看起来好像很难，很复杂

[jinxinjituan8088]

看起来好像很难，很复杂

[super712]

有待研究

[freehei]

搞这么复杂何必，弄个Treesize还带图形化界面的。

[haha_jerry_hehe]

[Quote=引用 64 楼 的回复:]
有待研究
[/Quote]
+1

[刘兄弟]

引用 17 楼 maco_wang 的回复:

--剩余大小
exec master..xp_fixeddrives 
exec master..xp_availablemedia 2
exec xp_availablemedia 2

问下 drive MB free C 16422 D 33871 E 80145 F 319908 =========================== name low free high free media type C:\ 40284160 4 2 D:\ 1157156864 8 2 E:\ -1860628480 19 2 F:\ 440561664 78 2 那我这个表示了啥子情况呢?