When working on a customer's Microsoft SQL Server, it often helpful to know what edition it is and what service packs are install. This is a Transact-SQL query that will return (almost) everything one needs to know about the SQL Server instance:
SELECT SERVERPROPERTY('machinename') AS 'Server Name', SERVERPROPERTY('instancename') AS 'Instance Name', SERVERPROPERTY('productversion') AS 'SQL Server Version', SERVERPROPERTY('productlevel') AS 'Product Level', SERVERPROPERTY('edition') AS 'SQL Server Edition', SERVERPROPERTY('collation') AS 'Default Collation', SERVERPROPERTY('licensetype') AS 'License Type';
The results returned by this query are:
Server Name – The name of the computer on which SQL Server is installed.
Instance Name – If SQL Server was installed as the default instance, this value will be NULL. Otherwise, it will name of the instance.
SQL Server Version – The version of the SQL Server instance. This can be used to determine what updates have been applied to the instance.
Product Level – The current installed Service Pack. If this value is RTM (Release To Manufacturing), then no service pack is installed.
SQL Server Edition – This is the edition of the SQL Server instance. Possible values are “Express Edition”, “Workgroup Edition”, “Web Edition”, “Standard Edition”, or “Enterprise Edition”. The edition determines what features are available in the SQL Server instance.
Default Collation – The default server collation value that was selected when the SQL Server instance was installed.
License Type – The type of client licenses used by the SQL Server instance. If this value is DISABLED, then the license type is per-processor (which is anonymous access).
This query works with all editions of SQL Server 2005 and higher.