Sometimes I want to find out how many rows are in each user table in a database. The following SQL helps me determine this.

select 'SELECT COUNT(*) AS ' +
'''' +
RTRIM ( name ) +
'''' +
' FROM ' +
RTRIM ( name )
from sysobjects where type = 'U'
order by name

There are a variety of ways to determine which version of SQL Server you are running. While the @@version information will tell you the current version
SELECT @@version
SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9))

The following code will as well, and in a slightly different format:
SELECT 'SQL Server '