ProjectSMM.com
Gonzo TechNet
How to retrieve and display Microsoft SQL Server & Database information
SQL 2000 and 2005 SERVER Version information
To display the current version of Microsoft SQL Server, run the following query against the target server using QueryAnalyzer:
SELECT
SERVERPROPERTY('ProductVersion') AS Version
,SERVERPROPERTY ('ProductLevel') AS SP_Level
,SERVERPROPERTY ('edition') AS Edition
-- OR --
SELECT
@@ServerName AS ServerName
,SERVERPROPERTY('ServerName') AS ServerName2
,SERVERPROPERTY('machinename') AS MachineName
,SERVERPROPERTY('InstanceName') AS InstanceName
,SERVERPROPERTY('ProductVersion') AS Version
,SERVERPROPERTY ('ProductLevel') AS SP_Level
,SERVERPROPERTY ('edition') AS Edition
,@@VERSION AS VersionInfo
Compare the results of your query to the tables below to find the version of SQL 2005 or 2000 you are running:
| SQL Server 2005 Version and Level |
@@VERSION |
Product Level |
| Database Components SP2 |
2005.90.3042 |
SP2 |
| Database Components SP1 |
2005.90.2047 |
SP1 |
| SQL 2005 Original Release |
2005.90.1399 |
RTM |
| SQL Server 2000 Version and Level |
VERSION |
Product Level |
| Database Components SP4 |
8.00.2039 |
SP4 |
| Database Components SP3, SP3a or MSDE 2000 Release A. |
8.00.760 |
SP3 |
| Database Components SP2 |
8.00.534 |
SP2 |
| Database Components SP1 |
8.00.384 |
SP1 |
| SQL Server 2000 Original Release |
8.00.194 |
RTM |
If you need to know the version information for SQL 6.5 or SQL 7.0, try this link to the Microsoft Knowledge Base:
SQL 2005 Database information
SELECT
[name] as DBName
,databasepropertyex(name, 'Status') as DBStaus
,databasepropertyex(name, 'Recovery') as RecoveryModel
,compatibility_level
FROM sys.databases
SQL 2000 Database information
SELECT
[name] as DBName
, databasepropertyex(name, 'Recovery') as RecoveryModel
,cmptlevel
FROM master.dbo.sysdatabases
ORDER BY [name]
SQL 7.0 Database information
SELECT
[name] AS DBName
,status
,CASE
when status & 4 > 0 then 'BULK_LOGGED'
WHEN status & 8 > 0 THEN 'SIMPLE'
ELSE 'FULL'
END AS RecoveryModel
,cmptlevel
FROM master.dbo.sysdatabases
ORDER BY [name]