sql语句2

--设置不显示返回行数
set nocount on
go
--查询服务器SQL版本及其详细信息
exec master..xp_msver
go
--查询实例名和服务器名
select @@SERVICENAME as SERVICENAME, @@SERVERNAME as SERVERNAME,@@VERSION as [VERSION]
go
--查询SQL服务状态、进程ID、启动状态、启动时间及详细信息
select servicename,startup_type_desc,status_desc,process_id,last_startup_time,service_account,[filename] 
from master.sys.dm_server_services
go
--查询SQL服务器IP地址，端口及执行本次查询的IP地址（本机LOCAL查询不显示IP地址）
SELECT local_net_address 服务器IP地址,local_tcp_port AS 服务端口,client_net_address AS 客户端IP地址
FROM sys.dm_exec_connections WHERE session_id = @@SPID
go
--查询服务器所有磁盘可用空间
EXEC master.dbo.xp_fixeddrives
go
--查询服务器本实例下所有非系统数据库文件路径、实际文件大小等详细信息
select database_id,cast(db_name(database_id) as nvarchar) as dbName,cast([name] as nvarchar) as [name],
[file_id],cast([type_desc] as nvarchar) as [type_desc],physical_name,state_desc,size/125.0 as MB--/1048
from sys.master_files where database_id > 4 
go
--查看数据库文件所在磁盘的空间使用情况
WITH T1 AS (
SELECT DISTINCT
REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) AS Free_Space_GB
FROM  sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)
SELECT
cast(Drive_Name as nvarchar) as [盘符],
Total_Space_GB as [总空间GB],
Total_Space_GB-Free_Space_GB AS [已用GB],
Free_Space_GB as [可用GB],
CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS [可用比%]
FROM T1