無需登入作業系統,僅用 T-SQL 即可查詢 SQL Server 實例所在伺服器的硬體規格與 OS 版本。本文整理適用於 SQL Server 2005~2022 的完整查詢腳本,涵蓋 CPU 型號/核心/超執行緒、實體記憶體/使用量、磁碟容量/使用率、Windows 版本/Service Pack/語言,並提供取得伺服器名稱與 IP 的多種方法。
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString';
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count / hyperthread_ratio AS [Physical CPU Count] FROM sys.dm_os_sys_info OPTION (RECOMPILE);
CREATE TABLE #Tmp ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000)); INSERT INTO #Tmp EXEC xp_msver; SELECT Internal_Value AS VirtualCPUCount FROM #Tmp WHERE Name = 'ProcessorCount'; DROP TABLE #Tmp;
DECLARE @xp TABLE (idx INT, c_name VARCHAR(100), int_val FLOAT, c_val VARCHAR(128));
INSERT INTO @xp EXEC('master..xp_msver');
WITH ProcessorInfo AS (
SELECT
cpu_count / hyperthread_ratio AS PhysicalCPUs,
CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE (cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio) END AS CoresPerCPU,
CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio)) END AS TotalCores,
cpu_count AS LogicalCPUs,
(SELECT c_val FROM @xp WHERE c_name = 'Platform') AS Platform
FROM sys.dm_os_sys_info
)
SELECT PhysicalCPUs, CoresPerCPU, TotalCores, LogicalCPUs,
LTRIM(RIGHT(Platform, CHARINDEX('x', Platform) - 1)) AS CPU_Category
FROM ProcessorInfo;
-- 虛擬機修正版:hyperthread_ratio 即為每插槽核心數
WITH ProcessorInfoVM AS (
SELECT
cpu_count / hyperthread_ratio AS PhysicalCPUs,
hyperthread_ratio AS CoresPerCPU,
cpu_count AS TotalCores,
cpu_count AS LogicalCPUs,
(SELECT c_val FROM @xp WHERE c_name = 'Platform') AS Platform
FROM sys.dm_os_sys_info
)
SELECT PhysicalCPUs, CoresPerCPU, TotalCores, LogicalCPUs,
LTRIM(RIGHT(Platform, CHARINDEX('x', Platform) - 1)) AS CPU_Category
FROM ProcessorInfoVM;
CREATE TABLE #Tmp ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000)); INSERT INTO #Tmp EXEC xp_msver; SELECT Internal_Value/1024 AS PhysicalMemory_MB FROM #Tmp WHERE Name = 'PhysicalMemory'; DROP TABLE #Tmp;
SELECT CEILING(physical_memory_in_bytes*1.0/1024/1024/1024) AS [Physical Memory GB] FROM sys.dm_os_sys_info OPTION (RECOMPILE);
SELECT CEILING(physical_memory_kb*1.0/1024/1024) AS [Physical Memory GB] FROM sys.dm_os_sys_info OPTION (RECOMPILE);
SELECT CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024) AS [Physical Memory GB], CAST(available_physical_memory_kb * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [Available GB], CAST((total_physical_memory_kb - available_physical_memory_kb) * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [Used GB], CAST(system_cache_kb * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [System Cache GB] FROM sys.dm_os_sys_memory;
SET NOCOUNT ON;
DECLARE @Result INT, @Obj INT, @Drv CHAR(1), @TotalSize VARCHAR(20), @Out INT;
DECLARE @UnitMB BIGINT = 1048576;
CREATE TABLE #Disk (DiskCD CHAR(1), FreeMB INT, TotalMB INT);
INSERT #Disk(DiskCD, FreeMB) EXEC master.dbo.xp_fixeddrives;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @Obj OUT;
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #Disk ORDER BY DiskCD;
OPEN Cur; FETCH NEXT FROM Cur INTO @Drv;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @Result = sp_OAMethod @Obj, 'GetDrive', @Out OUT, @Drv;
EXEC @Result = sp_OAGetProperty @Out, 'TotalSize', @TotalSize OUT;
UPDATE #Disk SET TotalMB = CAST(@TotalSize AS BIGINT) / @UnitMB WHERE DiskCD = @Drv;
FETCH NEXT FROM Cur INTO @Drv;
END
CLOSE Cur; DEALLOCATE Cur;
EXEC sp_OADestroy @Obj;
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
SELECT DiskCD AS [Drive],
STR(TotalMB*1.0/1024,6,2) AS [Total GB],
STR((TotalMB-FreeMB)*1.0/1024,6,2) AS [Used GB],
STR(FreeMB*1.0/1024,6,2) AS [Free GB],
STR((TotalMB-FreeMB)*100.0/TotalMB,6,2) AS [Used %],
STR(FreeMB*100.0/TotalMB,6,2) AS [Free %]
FROM #Disk;
DROP TABLE #Disk;
前置條件:需啟用 Ole Automation Procedures(生產環境建議用完即關)。
CREATE TABLE #Lang (LangDtl NVARCHAR(64), LangID INT);
INSERT #Lang VALUES
(N'English - United States',1033),(N'English - United Kingdom',2057),
(N'Chinese - PRC',2052),(N'Chinese - Singapore',4100),
(N'Chinese - Taiwan',1028),(N'Chinese - Hong Kong SAR',3076),
(N'Chinese - Macao SAR',5124);
WITH OSVer AS (
SELECT 'Windows 10' AS Name, '10.0*' AS Ver UNION ALL
SELECT 'Windows Server 2016', '10.0*' UNION ALL
SELECT 'Windows 8.1', '6.3*' UNION ALL
SELECT 'Windows Server 2012 R2', '6.3' UNION ALL
SELECT 'Windows 8', '6.2' UNION ALL
SELECT 'Windows Server 2012', '6.2' UNION ALL
SELECT 'Windows 7', '6.1' UNION ALL
SELECT 'Windows Server 2008 R2', '6.1' UNION ALL
SELECT 'Windows Server 2008', '6.0' UNION ALL
SELECT 'Windows Vista', '6.0' UNION ALL
SELECT 'Windows Server 2003 R2', '5.2' UNION ALL
SELECT 'Windows Server 2003', '5.2' UNION ALL
SELECT 'Windows XP 64-Bit', '5.2' UNION ALL
SELECT 'Windows XP', '5.1' UNION ALL
SELECT 'Windows 2000', '5.0'
)
SELECT o.Name AS [OS Version],
w.windows_service_pack_level AS [SP Level],
l.LangDtl AS [Language]
FROM sys.dm_os_windows_info w
JOIN OSVer o ON w.windows_release LIKE o.Ver
JOIN #Lang l ON l.LangID = w.os_language_version;
DROP TABLE #Lang;
注意:相同版本號可能對應多種 OS(如 6.1 同時對應 Win7 與 WS2008 R2),需結合環境判斷。
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT @@SERVERNAME AS InstanceName;
SELECT HOST_NAME() AS HostName;
方式 A:xp_cmdshell(需高權限,安全風險)
EXEC sp_configure 'show advanced options',1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE;
CREATE TABLE #IP (Line VARCHAR(200));
INSERT #IP EXEC xp_cmdshell 'ipconfig';
SELECT DISTINCT LTRIM(RTRIM(SUBSTRING(Line, CHARINDEX(':',Line)+1, 200))) AS IPv4
FROM #IP WHERE Line LIKE '%IPv4%';
DROP TABLE #IP;
EXEC sp_configure 'xp_cmdshell',0; RECONFIGURE;
方式 B:DMV(安全、無需額外權限)
SELECT
CONVERT(NVARCHAR(128), SERVERPROPERTY('SERVERNAME')) AS ServerName,
local_net_address AS [SQL Server IP],
client_net_address AS [Client IP]
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
整合上述腳本即可構建「資料庫端硬體/OS 巡檢報表」,納入日常巡檢或自動化監控,實現無代理、輕量級的基礎設施可視化。