When running the following script, will get you a verbose of the SQL server installed. This will get you the Service Pack number, version and other information which includes Product Level and other interesting stuff.
Note: if you would like to get a simplified information on the SQL server simply run the SELECT @@VERSION on your SQL while using the Master database, but if you want to know more in detail, use the script below.
SELECT @@VERSION
GO
SELECT SERVERPROPERTY('ProductVersion') 'Version', SERVERPROPERTY ('ProductLevel') 'Service Pack', SERVERPROPERTY ('Edition') 'Edition'
GO
-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- There doesn't seem to be a correlation between the key names here and SERVERPROPERTY values;
-- for example, 'ProductVersion' is returned by xp_msver, but 'ProductLevel' is not.
xp_msver
GO
-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- For SQL Server 2008, DBMS_VER returns "Microsoft SQL Server Yukon - 10.0.1600".
sp_server_info
GO
-- Stored procedure, not documented in Books Online, that returns the version string.
sp_MSgetversion
GO
-- @@MicrosoftVersion returns a decimal value which, when converted to varbinary, yields (for example) 0x09000FC3.
-- Enter "convert 0x0FC3 to decimal" in Google, and the result is 4035. Thus the overall MicrosoftVersion is 09.00.4035.
SELECT CONVERT(VARBINARY, @@MicrosoftVersion)
GO
-- get version info from the registry (SQL 2005 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='SP'
GO
-- get version info from the registry (SQL 2008 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='SP'
GO