关于获取服务器基本信息SQL

SET NOCOUNT ON

IF NOT EXISTS(
       SELECT * FROM sys.configurations with(nolock)
       WHERE name like 'xp_cmdshell'
              AND value_in_use = 1
)
BEGIN

              EXEC sp_configure 'show advanced options', 1;
              RECONFIGURE;

              EXEC sp_configure 'xp_cmdshell', 1;
              RECONFIGURE;
END

DECLARE @s_info table
(
       id int identity(1,1),
       col varchar(1000)
)

DECLARE
       @os varchar(200),
       @os_version varchar(200),
       @system_manufacturer varchar(100),
       @system_model varchar(100),
       @system_type varchar(100),
       @cpu varchar(1000),
       @sid int,
       @bid int

INSERT @s_info
EXEC xp_cmdshell 'systeminfo'
             

SELECT TOP 1
       @os = STUFF(REPLACE(LTRIM(RTRIM(col)),'OS Name:',''),1,19,'')
FROM @s_info
WHERE col LIKE '%OS Name%'

SELECT TOP 1
       @os_version = STUFF(REPLACE(LTRIM(RTRIM(col)),'OS Version:',''),1,16,'')
FROM @s_info
WHERE col LIKE '%OS Version%'

SELECT TOP 1
       @system_manufacturer = STUFF(REPLACE(LTRIM(RTRIM(col)),'System Manufacturer:',''),1,7,'')
FROM @s_info
WHERE col LIKE '%System Manufacturer%'

SELECT TOP 1
       @system_model = STUFF(REPLACE(LTRIM(RTRIM(col)),'System Model:',''),1,14,'')
FROM @s_info
WHERE col LIKE '%System Model%'

SELECT TOP 1
       @system_type = STUFF(REPLACE(LTRIM(RTRIM(col)),'System Type:',''),1,15,'')
FROM @s_info
WHERE col LIKE '%System Type%'

SELECT TOP 1
       @sid = id
FROM @s_info
WHERE col LIKE '%Processor(s)%'

SELECT TOP 1
       @bid = id
FROM @s_info
WHERE col LIKE '%BIOS Version%'


SET @cpu = CONVERT(varchar(1000),(
       SELECT
              Info = STUFF(col,1,27,'') 
       FROM @s_info
       WHERE id >@sid AND id < @bid
       FOR XML RAW('CPU')
))



--SELECT @os,@os_version,@system_manufacturer,@system_model,@system_type,@cpu


SELECT
        cpu_count / hyperthread_ratio AS PhysicalCPU,
        cpu_count / (cpu_count / hyperthread_ratio) AS Hyperthreads,
        CONVERT(decimal(10,2),physical_memory_in_bytes / (1024. * 1024. * 1024.)) AS PhysicalMemoryGB,
        CASE WHEN CONVERT(varchar(100),SERVERPROPERTY('ProductVersion')) LIKE '8.0%'
                                THEN '2000'
                WHEN CONVERT(varchar(100),SERVERPROPERTY('ProductVersion')) LIKE '9.0%'
                                THEN '2005'
                WHEN CONVERT(varchar(100),SERVERPROPERTY('ProductVersion')) LIKE '10.0%'
                                THEN '2008'
                WHEN CONVERT(varchar(100),SERVERPROPERTY('ProductVersion')) LIKE '10.5%'
                                THEN '2008 R2'
                WHEN CONVERT(varchar(100),SERVERPROPERTY('ProductVersion')) LIKE '11.0%'
                                THEN '2012'
                ELSE 'Unknown' END AS SQLVersion,
        SERVERPROPERTY('Edition') AS Edition,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('ProductLevel') AS ProductLevel,
        SERVERPROPERTY('Collation') AS Collation,
        SERVERPROPERTY('MachineName') AS MachineName,
        SERVERPROPERTY('InstanceName') AS InstanceName,
        SERVERPROPERTY('ServerName') AS ServerName,
        SERVERPROPERTY('IsClustered') AS IsClustered,
        SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
        SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
              @os AS OSName,
              @os_version AS OSVersion,
              @system_manufacturer AS SystemManufacturer,
              @system_model AS SystemModel,
              @system_type AS SystemType,
              @cpu AS CpuDetail
FROM sys.dm_os_sys_info with(nolock)