James Wiseman

I’ve been checking whether or not service packs have been applied on SQL Server Installations. I keep forgetting the script that I run to check this,  and having to look it up.

So, here it is:

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

This seems to work in SQL 2000, 2005 and 2008

Taken from this Microsoft link:

http://support.microsoft.com/kb/321185

You could also try

SELECT @@version


Which will give you everything (and more) in a single string:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)