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,

case

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

end

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