What service pack is on my SQL Server?
Quick way to determine which SP is on your SQL Server:
"If you are a DBA in a big SQL Server database shop, keeping track of what service packs are on each SQL Server can become daunting. Here are a couple of scripts that help you determine what service pack you are running on a SQL Server. I am also including all the SQL Server product version numbers and a link for your pleasure." Read the article at TechRepublic.
Update: For a quick how-to in SQL Server 2005. Open up SQL Server Management Studio, and run the following query on the master database.