Archive for the ‘Instance Level Info’ Category

Querying the SQL Server Instance Level Info

You are interested in finding out the SQL Server version, edition, service pack, authentication mode, clustering information, collation, the number of logical CPUs, and the server memory in megabytes. Sure, you can right-click the instance in the Object Explorer then go to Properties to view these information. But, what if you have to find these instance level information for 25+ instances? It will be a royal pain in the behind to go to each of the 25+ instances and then typing or copying and pasting these information to Excel if you need to document them. This is where the SERVERPROPERTY() function and the sys.dm_os_sys_info Dynamic Management View (DMV) can save the day or at least save you an hour or more. Here we go:

-- SQL Server Instance Level Info
SELECT	
	SERVERPROPERTY('ServerName') AS [InstanceName],
	SERVERPROPERTY('ProductVersion') AS [VersionBuild],
	SERVERPROPERTY ('Edition') AS [Edition],
	SERVERPROPERTY('ProductLevel') AS [ServicePack],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [WindowsAuthentication],
	SERVERPROPERTY('IsClustered') AS [IsClustered],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],
	SERVERPROPERTY('Collation') AS [Collation],
	[cpu_count] AS [CPUs],
	[physical_memory_in_bytes]/1048576 AS [RAM (MB)]
FROM	
	[sys].[dm_os_sys_info]

OUTPUT: Screen-shot

REFERENCES:

SERVERPROPERTY()
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

sys.dm_os_sys_info
http://msdn.microsoft.com/en-us/library/ms175048(v=sql.105).aspx