All SQL Version info in one function

,

Just run the code in the database you want and add the following in your T-SQL scripts:

select * from dbo.udf_SQLVersionInfo()

This returns several columns. If you just want to check on a version number, you could run:

if (select ProductValue from dbo.udf_SQLVersionInfo()) > 10.0 ...

update: removed licensetype, support for MSSQL 2000, added HadrStatus, Isclustered and FulltextInstalled

print '-- deploy function [dbo].[udf_sqlversioninfo] on \\' + @@SERVERNAME + '\' + DB_NAME()
print '-- time: ' + cast(current_timestamp as varchar)
print '-- Build: Fri May 4 2012 at 3:48:19pm, (v12.125)'



--#region drop if exists

if exists (select 1 from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'udf_sqlversioninfo' and ROUTINE_SCHEMA='dbo' and ROUTINE_TYPE='FUNCTION')
drop function [dbo].[udf_sqlversioninfo];
go

--#endregion


--#region create function udf_sqlversioninfo

create function dbo.udf_sqlversioninfo
()
/*
    name udf_sqlversioninfo
    returns        @productinfo table(ProductVersion sysname, ProductValue numeric(18,7), ProductName varchar(16), ProductLevel varchar(8), Major int, Minor int, Build int, BuildVersion int, Edition sysname, EngineEdition varchar(16), IsClustered varchar(16), IsFulltextInstalled varchar(16), HadrStatus varchar(16))
    purpose Returns SQL Version info in a table
    parameters .
    features    .
    version        1.1
    uses        .
*/
returns @productinfo table(ProductVersion sysname, ProductValue numeric(18,7), ProductName varchar(16), ProductLevel varchar(8), Major int, Minor int, Build int, BuildVersion int, Edition sysname, EngineEdition varchar(16), IsClustered varchar(16), IsFulltextInstalled varchar(16), HadrStatus varchar(16))
as
    begin
        insert into @productinfo(ProductVersion, ProductLevel, Major, Minor, Build, BuildVersion, Edition)
        select    cast(serverproperty('Productversion') as sysname)
                , cast(serverproperty('ProductLevel') as sysname)
                , parsename(cast(serverproperty('Productversion') as sysname),4)
                , parsename(cast(serverproperty('Productversion') as sysname),3)
                , parsename(cast(serverproperty('Productversion') as sysname),2)
                , parsename(cast(serverproperty('Productversion') as sysname),1)
                , cast(serverproperty('Edition') as sysname)
        
        update @productinfo
        set Productvalue = cast(
                                    parsename(cast(serverproperty('Productversion') as sysname),4)
                                    + '.'
                                    + parsename(cast(serverproperty('Productversion') as sysname),3)
                                    + parsename(cast(serverproperty('Productversion') as sysname),2)
                                    + parsename(cast(serverproperty('Productversion') as sysname),1)
                                 as numeric(18,7));


      update @productinfo
        set ProductName = 'MSSQL ' + case
                                when [Major] = 8 then '2000'
                                when [Major] = 9 then '2005'
                                when Major = 10 and Minor = 0 then '2008'
                                when Major = 10 and Minor = 50 then '2008R2'
                                when Major = 11 then '2012'
                                else cast(Major as varchar) + '.' + cast(Minor as varchar)
                            end ;                            


      update @productinfo
        set EngineEdition = case cast(serverproperty('EngineEdition') as int)
                                when 1 then 'Personal'
                                when 2 then 'Standard'
                                when 3 then 'Enterprise'
                                when 4 then 'Express'
                                when 5 then 'Azure'
                                else 'unknown (' + cast(serverproperty('EngineEdition') as varchar) + ')'
                            end;
                            
        update @productinfo
        set IsFullTextInstalled = case cast(serverproperty('IsFullTextInstalled') as int)
                            when 1 then 'Installed'
                            when 0 then 'Not installed'
                        end;
                        
        update @productinfo
        set Isclustered = case cast(serverproperty('IsClustered') as int)
                            when 1 then 'Clustered'
                            when 0 then 'Not clustered'
                        end;


      update @productinfo
        set HadrStatus = case cast(serverproperty('IsHadrEnabled') as int)
                            when 1 then
                                case cast(serverproperty('HardManagerStatus') as int)
                                    when 0 then 'Enabled - pending'
                                    when 1 then 'Enabled - running'
                                    when 2 then 'Enabled - failed'
                                end
                            when 0 then 'disabled'
                            else 'Unavailable'
                        end;


                              
        return;
    end
go

--#endregion

Rate

2.83 (6)

Share

Share

Rate

2.83 (6)