SQL Server HotFix History Analysis

use mssqltips
set nocount on;
 
declare @build varchar(30), @description varchar(600), @ReleaseDate varchar(30), @weblink varchar(600);
declare @loc int, @loc2 int, @crlf char(2) = char(0x0d) + char(0x0a);
declare @cur cursor;
 
set @cur = cursor for
select * from dbo.sqlver 
where charindex(';', weblink) > 0;
 
open @cur;
fetch next from @cur into @build, @description, @releasedate, @weblink;
while @@FETCH_STATUS = 0
begin
   if @description like 'CU%'
   begin
      select @loc = charindex(@crlf, @description), @loc2 = charindex(';', @weblink);
      update  dbo.sqlver--dbo.SQLVer
      set [Description] = SUBSTRING([Description], 1, @loc-1), WebLink = substring(Weblink, 1, @loc2-1)
      where current of @cur;
      insert into dbo.sqlver (build, [Description], ReleaseDate, WebLink)
      select @build, substring(@description, @loc+2, 200), @ReleaseDate, substring(@weblink, @loc2+1, 200)
   end
   else
   begin
      select @loc = charindex(':', @description), @loc2 = charindex(';', @weblink);
      update dbo.sqlver --dbo.SQLVer
      set [Description] = SUBSTRING([Description], 1, @loc-1), WebLink = substring(Weblink, 1, @loc2-1)
      where current of @cur;
 
      insert into dbo.sqlver (build, [Description], ReleaseDate, WebLink)
      select @build, substring(@description, @loc+2, 200), @ReleaseDate, substring(@weblink, @loc2+1, 200);
 
 
   end
 
   fetch next from @cur into @build, @description, @releasedate, @weblink;
 
end
close @cur;
deallocate @cur;
 
-- cleanup some depreciated (or replaced) CUs
delete from dbo.sqlver
where description like '%(replaced)%'

import-module sqlserver -DisableNameChecking;
$qry = @"
select WebLink from dbo.SQLVer
where Description like 'cu%'
and weblink is not null;
"@;
$links = invoke-sqlcmd -ServerInstance localhost -Database MSSQLTips -Query $qry;
 
$repo_dt = new-object system.data.datatable;
$col = new-object System.Data.DataColumn ('bugnum', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('kbnum', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('description', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('fixarea', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('platform', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('kb_url', [system.string]);
$repo_dt.Columns.Add($col);
 
$col = new-object System.Data.DataColumn ('cu_url', [system.string]);
$repo_dt.Columns.Add($col);
 
foreach ($url in $links.weblink) #{$url}
{
    try 
    {
        Write-Host $url -ForegroundColor Yellow;
        $dt=Get-SQLCUDetail -SQLCU $url  -IncludeLink;  
 
        if ($dt.Columns.count -eq 7) #contains [platform] column
        {  
            foreach($r in $dt.Rows)
            {
               $dr = $repo_dt.NewRow();
               $dr[0]=$r[0];
               $dr[1]=$r[1];
               $dr[2]=$r[2];
               $dr[3]=$r[3];
               $dr[4]=$r[4]
               $dr[5]=$r[5];
               $dr[6]=$r[6];
               $repo_dt.Rows.add($dr);
            }
        }
        elseif ($dt.columns.Count -eq 6) #deos not contain [platfrom] but contains [fix area] col
        {
           foreach ($r in $dt.rows)
           {
               $dr = $repo_dt.NewRow();
               $dr[0]=$r[0];
               $dr[1]=$r[1];
               $dr[2]=$r[2];
               $dr[3]=$r[3];
               $dr[5]=$r[4];
               $dr[6]=$r[5];
               $repo_dt.Rows.add($dr);
            }
        } #elseif
        else #only 5 columns, no [platform] and [fix area]
        {
           foreach ($r in $dt.rows)
           {
               $dr = $repo_dt.NewRow();
               $dr[0]=$r[0];
               $dr[1]=$r[1];
               $dr[2]=$r[2];
               $dr[5]=$r[3];
               $dr[6]=$r[4];
               $repo_dt.Rows.add($dr);
            }
        } #else
        # Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips -SchemaName dbo -TableName SQLCU_Detail -InputData $dt;
    } #try
    catch
    { Write-Error $_; }
}
#write to sql table dbo.SQLCU_Detail
Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips -SchemaName dbo -TableName SQLCU_Detail -InputData $repo_dt;
			

-- populate dbo.SQLVer_Analysis
use MSSQLTips;
 
insert into dbo.SQLVer_Analysis (build, [Description], ReleaseDate, [Name])
select build, [description], [ReleaseDate]
, brief= case when [Description] like 'CU%' then replace(substring([description], 1, charindex(' (KB', description)), ' Microsoft ', ' ') 
              when [Description] like '%RTM' then [Description]  
           when [Description] like '% Service Pack [1-9]%' then substring(replace([Description], 'Microsoft ', ''), 1, iif(charindex(' (',replace([Description], 'Microsoft ', ''))=0, 100,charindex(' (',replace([Description], 'Microsoft ', '')) ))
        else substring([description], 1, charindex(' (KB', description)) end
from dbo.SQLVer
where (Description like '%CU[1-9]%' or [Description] like '%RTM' or [Description] like '%Service Pack [1-9]%')
and convert(decimal(2,0),left(build,2)) >=11
and  [Description] not like 'MS%' and  [Description] not like 'security%' and  [Description] not like 'fix%' and  [Description] not like 'on%'
order by  build --convert(decimal(2,0),left(build,2)) asc--, -- cast(Releasedate as datetime) asc
 
-- a small cleanup
update dbo.SQLVer_Analysis set Name=replace(name, ' RTM', '')
where name like 'CU% 2016 RTM'
go
 
 
-- populate ParentID and RootID
 
declare @cur cursor;
declare @id int, @name varchar(100); 
declare @first_id int, @last_id int
 
select top 1 @first_id = id from dbo.SQLVer_Analysis
where name like '%rtm'
order by id asc
 
set @cur = cursor for 
select id
from dbo.SQLVer_Analysis
where id > @first_id
and name like '%rtm'
order by id
 
open @cur;
fetch next from @cur into @id
while @@fetch_status = 0
begin
    set @last_id = @id
   update dbo.SQLVer_Analysis
   set RootID = @first_id
   where id >= @first_id and id < @last_id;
   
   set @first_id = @last_id
   fetch next from @cur into @id
 
end
select @last_id = max(id) + 1 from dbo.SQLVer_Analysis;
 
   update dbo.SQLVer_Analysis
   set RootID = @first_id
   where id >= @first_id and id < @last_id;
close @cur;
 
 
-- now we need to update parentid
 
select top 1 @first_id = id from dbo.SQLVer_Analysis
where name like '%rtm'
order by id asc
 
set @cur = cursor for 
select id
from dbo.SQLVer_Analysis
where name like '%rtm' or name like '% Service Pack [1-9]'
and id > @first_id
order by id;
open @cur;
fetch next from @cur into @id
while @@fetch_status = 0
begin
    set @last_id = @id
   update dbo.SQLVer_Analysis
   set ParentID = @first_id
   where id > @first_id and id < @last_id;
   
   set @first_id = @last_id
   fetch next from @cur into @id
 
end
select @last_id = max(id) + 1 from dbo.SQLVer_Analysis;
 
   update dbo.SQLVer_Analysis
   set ParentID = @first_id
   where id > @first_id and id < @last_id;
close @cur;
go