Home > dba, sql > Get all SERVERPROPERTY values for SQL Server

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
Categories: dba, sql Tags:
  1. April 15th, 2011 at 10:39 | #1

    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

  2. April 15th, 2011 at 10:43 | #2

    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

  3. April 17th, 2011 at 10:06 | #3

    Good post. I can never remember all those and always have to look them up. This will come in very handy and surprised I didn’t think to do this myself.

  1. August 23rd, 2011 at 14:26 | #1

%d bloggers like this: