T-SQL系列之查看所有数据库大小、恢复模式等信息(MSSQL)

-- 查看所有数据库大小、恢复模式等信息

SELECT substring (DatabaseName,0,20) as DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink
FROM 
(SELECT DBID,
CASE Sum(size*8/1024) 
WHEN 0 THEN 1 
ELSE Sum(size*8/1024) 
END AS DataSize
FROM master..sysaltfiles
WHERE GroupID <> 0
GROUP BY DBID) q1
INNER JOIN
(SELECT DBID,
CASE Sum(size*8/1024) 
WHEN 0 THEN 1 
ELSE Sum(size*8/1024)
END AS LogSize
FROM master..sysaltfiles
WHERE GroupID = 0
GROUP BY DBID) q2 
ON q1.DBID = q2.DBID
INNER JOIN
(SELECT DBID, [name] AS DatabaseName,
CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Collation')) AS Collation,
CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,
CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))
WHEN 0 THEN '-'
WHEN 1 THEN 'Yes'
END  AS AutoClose,
  CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))
WHEN 0 THEN '-'
WHEN 1 THEN 'Yes'
END AS AutoShrink
FROM master.dbo.sysdatabases) q3
ON q1.DBID = q3.dbid
ORDER BY DatabaseName 
go

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值