Printed 2017/08/19 07:37AM

Two Ways to Get System and Server Information on SQL Server from Management Studio / Enterprise Manager

By Hugo Shebbeare, 2010/04/30


To collect information for your SQL Server Infrastructure, there are two ways I can recommend. 
The first, as mentioned from Technet recently, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.

exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"

-- result set is a table, with a row for each parameter


The second, and my preference as best pratice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):

select serverproperty('MachineName') MachineName

,serverproperty('ServerName') ServerInstanceName

,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled

,serverproperty('productVersion') ProductBuildLevel

,serverproperty('productLevel') SPLevel

,serverproperty('Collation') Collation_Type

,serverproperty('IsClustered') [IsClustered?]

,convert(varchar,getdate(),102) QueryDate,


when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'

else 'NotOnDRP' -- where you would replace the

--data protector string with your third party backup solution


 -- thanks to my highly organised DBA buddy Pollus Brodeur, for introducing me to ServerProperty command several years ago


To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.


References:  See all the recent Technet SQL Server Tips 


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.