Need a quick and easy way to gather basic information about the SQL Server instance you are on? Keep this script in your SSMS Template Explorer and the information is only a double-click away. This script works for SQL Server 2005 and SQL 2008 only because it uses the DMV sys.dm_os_sys_info. If you are on SQL Server 2000 just highlight the code down to the comment and execute.
SELECT
@@ServerName AS ServerName
,Case Left(Convert(Varchar(128),SERVERPROPERTY(‘productversion’)),2)
When ‘8.’ Then ‘SQL 2000’
When ‘9.’ Then ‘SQL 2005′
When ’10’ Then ‘SQL 2008’
Else ‘Unknown’
End As SQLVersion
,SERVERPROPERTY(‘productlevel’) As ServicePackLevel
,SERVERPROPERTY(‘productversion’) As BuildNumber
,SERVERPROPERTY(‘edition’) As Edition
,SERVERPROPERTY(‘LicenseType’) As LicenseType
,SERVERPROPERTY(‘NumLicenses’) As NumLicenses
,CASE SUBSTRING(@@VERSION,PATINDEX(‘%Windows NT%’,@@VERSION),15)
WHEN ‘Windows NT 5.0’ THEN ‘Windows 2000’
WHEN ‘Windows NT 5.1’ THEN ‘Windows XP’
WHEN ‘Windows NT 5.2’ THEN ‘Windows Server 2003 or Windows XP (64-bit)’
WHEN ‘Windows NT 6.0’ THEN ‘Windows Server 2008’
WHEN ‘Windows NT 6.1’ THEN ‘Windows Server 2008 R2 or Windows 7’
ELSE ‘Unknown’
END AS WindowsVersion
,CASE WHEN SERVERPROPERTY(‘IsClustered’) = 1
THEN ‘Yes’
ELSE ‘No’
END AS IsClustered
–For SQL Server 2K run to here
,cpu_count AS LogicalCPUs
,cpu_count / hyperthread_ratio AS PhysicalCPUs
,max_workers_count AS Threads
,physical_memory_in_bytes/1048576 AS PhysicalMemory_MB
FROM
sys.dm_os_sys_info
Here is an example of the result set on SQL Server 2008: