Technical Article

ServerProperty query with case function

,

Tested with 2005/2008.

select 
serverproperty('BuildClrVersion') as BuildClrVersion 
,serverproperty('Collation') as Collation 
,serverproperty('CollationID') as CollationID 
,serverproperty('ComparisonStyle') as ComparisonStyle 
,serverproperty('ComputerNamePhysicalNetBIOS') as ComputerNamePhysicalNetBIOS 
,serverproperty('Edition') as Edition 
,case serverproperty('EditionID') 
when -1253826760 then 'Desktop'
when -1592396055 then 'Express'
when -1534726760 then 'Standard'
when 1333529388 then 'Workgroup'
when 1804890536 then 'Enterprise'
when -323382091 then 'Personal'
when -2117995310 then 'Developer'
when 610778273 then 'Enterprise Evaluation'
when 1044790755 then 'Windows Embedded SQL'
when 4161255391 then 'Express with Advanced Services'
else '???' end as EditionID 
,case serverproperty('EngineEdition') 
when 1 then 'Personal-Desktop'
when 2 then 'Standard'
when 3 then 'Enterprise'
when 4 then 'Express'
else '???' end as EngineEdition
,serverproperty('InstanceName') as InstanceName 
,serverproperty('IsClustered') as IsClustered 
,serverproperty('IsFullTextInstalled') as IsFullTextInstalled 
,serverproperty('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly 
,serverproperty('IsSingleUser') as IsSingleUser 
,serverproperty('LCID') as LCID 
,serverproperty('LicenseType') as LicenseType 
,serverproperty('MachineName') as MachineName 
,serverproperty('NumLicenses') as NumLicenses 
,serverproperty('ProcessID') as ProcessID 
,serverproperty('ProductVersion') as ProductVersion 
,serverproperty('ProductLevel') as ProductLevel 
,serverproperty('ResourceLastUpdateDateTime') as ResourceLastUpdateDateTime 
,serverproperty('ResourceVersion') as ResourceVersion 
,serverproperty('ServerName') as ServerName 
,serverproperty('SqlCharSet') as SqlCharSet 
,serverproperty('SqlCharSetName') as SqlCharSetName 
,serverproperty('SqlSortOrder') as SqlSortOrder 
,serverproperty('SqlSortOrderName') as SqlSortOrderName 
,serverproperty('FilestreamShareName') as FilestreamShareName 
,serverproperty('FilestreamConfiguredLevel') as FilestreamConfiguredLevel 
,serverproperty('FilestreamEffectiveLevel') as FilestreamEffectiveLevel

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating