Ever wanted to find out the version of your SQL Server in t-sql? Here's how!
Tuesday, April 07, 2009 9:37 PM
Have you ever wanted to use t-sql to find out the specific version of SQL Server you were running on? If you're writing version specific t-sql, this information will be invaluable, continue reading for a quick way to figure it out.
We all know developing on multiple db platforms can be a royal pain. But having to deal with different sql dialects can be a major PITA as well! The sneaky cousin to that problem is writing sql and procs for the same db platform but different versions! Have you ever noticed the t-sql facilities available to you in SQL Server 2008 but not in SQL Server 2000?
Well, I ran SMACK head first into one a few weeks ago, trying to get schema information from SQL Server via sql. The problem is, with SQL Server 2000, Microsoft lets you query the master db and it's objects. In SQL Server 2005 Microsoft has added a bunch of new system views which they encourage sql writer to use instead of going against the master tables. They reserve the right to alter/update/modify the system tables as they see fit. Therefore the views seem to be the way to go for new sql.
But what can you do if you have to support an older SQL Server 2000 client? I have one solution for you. I'm not naive enough to think this is the only way, so if you know of a another (maybe even better? :>) please comment and share! :>
Below is the sql which gets the version of the current SQL Server using the SERVERPROPERTY function. This starting point is key, this function is supported in SQL Server 2000 and better, so you know have a server supported way to get the server version, and not some hack. Next the major part of the version information is pulled out (based on a period separator. There is a simple if statement separating the different versions. Next for the older SQL Server versions, retrieving schema information goes against the master.dbo system tables and everything newer than SQL Server 2005 uses the sys views.
declare @SqlServerVersion nvarchar(max);
select @SqlServerVersion = cast(SERVERPROPERTY('ProductVersion') as nvarchar(max));
declare @MajorVersion int;
set @MajorVersion = left(@SqlServerVersion, charindex('.', @SqlServerVersion)-1)
declare @DBName varchar(max); set @DbName = 'Northwind'
declare @DBLogicalName varchar(max)
print 'SQL Server Version: ' + cast(@MajorVersion as varchar)
if(@MajorVersion < 9) --SQL Server 2000 or below
--physical and logical names
select name, filename
where master.dbo.sysaltfiles.filename =
where master.dbo.sysdatabases.name = @DBName and master.dbo.sysaltfiles.dbid = master.dbo.sysdatabases.dbid)
select name, filename from master.dbo.sysdatabases where name = @DBName
else --SQL Server 2005 and above, this uses the new view based architecture
--physical and logical names
where database_id = (
where name = @DBName) and
type = 0 /*0 seems to be data, 1 seems to be log*/ and
data_space_id <> 0 /*0 = log file*/
select name, database_id, state, state_desc
where name = @DBName
Now that you know how to differentiate between versions of SQL Server and can grab schema information, it's now time to go grab a coffee and get coding! :>
MSDN: Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views