How do you check what version of SQL Server for a database using TSQL?

Try

SELECT @@VERSION 

or for SQL Server 2000 and above the following is easier to parse 🙂

SELECT SERVERPROPERTY('productversion')
     , SERVERPROPERTY('productlevel')
     , SERVERPROPERTY('edition')

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