SSMS 16.x の情報の取得

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
      


declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
      


SELECT
(select Value from #SVer where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #SVer where Name = N'Language') AS [Language],
(select Value from #SVer where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],
(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@SmoRoot AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]

    drop table #SVer
      

SSMS vNext の情報の取得

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver       
if exists (select 1 from sys.all_objects where name = 'dm_os_host_info' and type = 'V' and is_ms_shipped = 1)
begin
    insert #SVer select t.*
        from sys.dm_os_host_info 
        CROSS APPLY (
        VALUES 
            (1001, 'host_platform', 0, host_platform),
            (1002, 'host_distribution', 0, host_distribution),
            (1003, 'host_release', 0, host_release),
            (1004, 'host_service_pack_level', 0, host_service_pack_level),
            (1005, 'host_sku', host_sku, '')                    
        ) t(id, [name], internal_value, [value]) 
    end
      


declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
      


SELECT
(select Value from #SVer where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #SVer where Name = N'Language') AS [Language],
(select Value from #SVer where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],
(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@SmoRoot AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation],
( select Value from #SVer where Name =N'host_platform') AS [HostPlatform],
( select Value from #SVer where Name =N'host_release') AS [HostRelease],
( select Value from #SVer where Name =N'host_service_pack_level') AS [HostServicePackLevel],
( select Value from #SVer where Name =N'host_distribution') AS [HostDistribution]

        drop table #SVer