I’ve often needed to work out what service pack level SQL is running or whether it is a Enterprise or Standard installation. It’s not obvious how to do this and it’s not as simple as going to Help –> About.
The following query will work in SQL 2000 and above. To run it, for example in SQL 2005, open SQL Server Management Studio, enter your credentials and click ‘New Query’ in the top left. Enter the follow, then hit the ‘Execute’ button:
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
This will return some values in the Result tab, for example the Service Pack level and edition that is running.