Get all SERVERPROPERTY values for SQL Server

A quick script to grab all the SERVERPROPERTY() values from a SQL instance in a table. Useful when you forget what the propertyname options are. Properties obtained from http://msdn.microsoft.com/en-us/library/ms174396.aspx.

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)
INSERT INTO @props(propertyname)
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'EditionID'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
 
SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props

5 thoughts on “Get all SERVERPROPERTY values for SQL Server

  1. Norman Kelm

    Hi Eric,

    Nice script, but since I’m learning PoSH I had to ask myself how to do this in PoSH.

    Here it is:
    Get-Item SQLSERVER:\SQL\\ | %{ $_.Properties} | Sort-Object Name | Select Name, Value

    Norman

    Reply
  2. Norman Kelm

    Ooops, some of my code got removed since I used lt and gt symbols.

    Get-Item SQLSERVER:\SQL\server\instance | %{ $_.Properties} | Sort-Object Name | Select Name, Value

    Reply
  3. Pingback: Eric Humphrey » Get All SESSIONPROPERTY Values for Your Session

  4. Pingback: Get All SESSIONPROPERTY Values for Your Session - SQL Server - SQL Server - Toad World

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.