SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


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 



Posted by npcrwill8 on 3 May 2010

"Processor count"  -- physical socketed CPU chips or "processor cores" ?  What does licensing use then ?

Posted by Hugo Shebbeare on 3 May 2010

Thank you for your questions MPCRWILL8: The processor cores value is the number of virtual processors given to the Database Engine. Thus Hyprethreaded ones, and multiple cores will be all included, not the physical sockets - unless they are the same number (which is doubtful these days of HT and quad cores, etc).

With respect to Licensing, there are thresholds for number of users when choosing CAL device or user  VS Per Processor - therefore, please read the licensing guide here (it is long, but thorough):


Leave a Comment

Please register or log in to leave a comment.