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 |
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
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
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.