Thursday, March 01, 2001 ..:: Home ::.. Register  Login
   TechTidBits (Blog)  

Ever wanted to find out the version of your SQL Server in t-sql? Here's how!

Apr 7

Written by:
Tuesday, April 07, 2009 9:37 PM  RssIcon

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
  from master.dbo.sysaltfiles 
  where master.dbo.sysaltfiles.filename = 
    (select master.dbo.sysdatabases.filename 
    from master.dbo.sysdatabases 
    where = @DBName and master.dbo.sysaltfiles.dbid = master.dbo.sysdatabases.dbid)
    --database names
  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
  select name, 
  from sys.master_files 
  where database_id = (
    select database_id 
    from sys.databases 
    where name = @DBName) and 
      type = 0 /*0 seems to be data, 1 seems to be log*/ and 
      data_space_id <> 0 /*0 = log file*/

  --database names
  select name, database_id, state, state_desc
  from sys.databases 
  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


Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Add Comment   Cancel 
Copyright 1999-2012 by   Terms Of Use  Privacy Statement