declare @fixFactDiscrepancies bit =0 -- set this to 1 for real corrections, but Please take a database backup before !!!
--set it to 0 to only show the discrepancies
-------------------------------------------------------------------------------
set nocount on
if @fixFactDiscrepancies = 0
print 'Correcting DateDim rows, Fact TABLEs and VIEWs, if discrepancies found ...';
else
print 'Read-only mode...'
print ''
declare @dec31Exists bit
set @dec31Exists=0
select @dec31Exists=1 from DateDim where DateKey=20201231
if @dec31Exists = 0 and @fixFactDiscrepancies = 1
begin
print 'inserting missing Dec 31 2020 row into DateDim'
insert into DateDim values (20201231, '20201231', 'Thursday', 31, 53, 'December', 12, 2020, 'Q4', 'Q2', 6, 2021, 0, 1, 1)
end
declare @dateCountAfter2021 int
set @dateCountAfter2021=0
select @dateCountAfter2021=count(*) from DateDim where DateKey>=20210101 --10956
if @dateCountAfter2021 < 10956 and @fixFactDiscrepancies = 1
begin
print 'running PopulateDateDim'
EXEC PopulateDateDim '20210101', '20501231'
end
declare @factCountTotal int = 0, @factCountCorrected int = 0
declare @factViewCountTotal int = 0, @factViewCountCorrected int = 0
declare @Months table (MonthName char(3),MonthValue tinyint, LastDay tinyint)
insert into @Months values
('Jan',1,31),
('Feb',2,28),
('Mar',3,31),
('Apr',4,30),
('May',5,31),
('Jun',6,30),
('Jul',7,31),
('Aug',8,31),
('Sep',9,30),
('Oct',10,31),
('Nov',11,30),
('Dec',12,31)
declare @factName sysname
declare c cursor local FORWARD_ONLY READ_ONLY for
select distinct left(name,datalength(name)/2-13)+'vw' as name from sys.check_constraints where name like '%\_____\____\_Chk' escape '\' and definition like '%DateKey%' order by 1
open c; while 1=1 begin; fetch c into @factName; if @@FETCH_STATUS<>0 break;
set @factViewCountTotal=@factViewCountTotal+1
declare @currentView nvarchar(max), @isViewCorrect bit, @selectClause nvarchar(max)
set @isViewCorrect = 1
set @selectClause=''
select @currentView=definition from sys.objects o join sys.sql_modules m on m.object_id = o.object_id where o.object_id = object_id('dbo.'+ @factName) and o.type = 'V'
set @factName=left(@factName,datalength(@factName)/2-2)
declare @factTableName sysname
declare @factTables table (name sysname, [Year] smallint, [Month] tinyint, YearMonth int)
delete @factTables
insert into @factTables (name) select name from sys.tables where name like @factName + '\_____\____' escape '\' order by name;
declare c2 cursor local FORWARD_ONLY READ_ONLY for
select name from @factTables;
open c2; while 1=1 begin; fetch c2 into @factTableName; if @@FETCH_STATUS<>0 break;
declare @Year smallint, @Month tinyint, @YearMonth int, @YearName char(4), @MonthName char(3)
set @MonthName=RIGHT(@factTableName,3)
set @YearName=RIGHT(@factTableName,8); set @YearName=left(@YearName,4)
set @year=cast(@YearName as smallint)
select @Month=MonthValue from @Months where MonthName=@MonthName
update @factTables
set [Year]=@Year, [Month]=@Month, YearMonth=@Year*100+@Month
where name=@factTableName
end; close c2; deallocate c2;
declare @minYear smallint, @minMonth tinyint, @maxYear smallint, @maxMonth tinyint, @minYearMonth int, @maxYearMonth int, @prevYearMonth int, @nextAvailableYear smallint, @nextAvailableMonth tinyint, @prevMonthLastDay int
select @minYear=min(YearMonth)/100, @minMonth=min(YearMonth) % 100, @maxYear=max(YearMonth)/100, @maxMonth=max(YearMonth) % 100, @minYearMonth=min(YearMonth), @maxYearMonth=max(YearMonth) from @factTables
declare @isFirstIteration bit =1
declare @correctView nvarchar(max)
set @correctView= ''
declare c3 cursor local FORWARD_ONLY READ_ONLY for
select name, [Year], [Month], YearMonth from @factTables order by YearMonth
open c3; while 1=1 begin; fetch c3 into @factTableName, @Year, @Month, @YearMonth; if @@FETCH_STATUS<>0 break;
set @factCountTotal=@factCountTotal+1
declare @chkStartDate int, @chkEndDate int
if @YearMonth = @maxYearMonth
begin
set @chkStartDate = @YearMonth*100 + 1
set @chkEndDate=null
end
else
begin
select top 1 @nextAvailableYear=[Year], @nextAvailableMonth=[Month]
from @factTables
where YearMonth>@YearMonth
order by YearMonth
if @nextAvailableMonth = 1 set @prevMonthLastDay=(@nextAvailableYear-1)*10000+1231
else set @prevMonthLastDay=(@nextAvailableYear*10000) + ((@nextAvailableMonth-1)*100) + (select LastDay from @Months where MonthValue=@nextAvailableMonth-1)
if @isFirstIteration = 1
set @chkStartDate = null
else
set @chkStartDate = @YearMonth*100 + 1
set @chkEndDate = @prevMonthLastDay
end
if @chkEndDate % 100 = 28
if ((@chkEndDate / 10000) % 4) = 0 set @chkEndDate = @chkEndDate+1
--1. check constraint
declare @currentChk nvarchar(max)
set @currentChk=''
select @currentChk=definition from sys.check_constraints where name = @factTableName + '_Chk'
set @currentChk = REPLACE(@currentChk,'(','')
set @currentChk = REPLACE(@currentChk,')','')
declare @correctChk nvarchar(max)
set @correctChk = ''
if @chkStartDate is not null
set @correctChk = '[DateKey]>='+ cast(@chkStartDate as nvarchar(max))
if @chkStartDate is not null and @chkEndDate is not null
set @correctChk = @correctChk + ' AND '
if @chkEndDate is not null
set @correctChk = @correctChk + '[DateKey]<='+ cast(@chkEndDate as nvarchar(max))
declare @chkScript nvarchar(max)
set @chkScript= ''
if @currentChk != @correctChk
begin
print '--current '+ @factTableName+'_chk: ' + @currentChk
if @currentChk != ''
set @chkScript = 'ALTER TABLE dbo.'+ @factTableName +' DROP CONSTRAINT '+ @factTableName +'_Chk; '
set @chkScript = @chkScript + ' ALTER TABLE [dbo].['+ @factTableName +'] WITH CHECK ADD CONSTRAINT ['+ @factTableName +'_Chk] CHECK ('+ @correctChk +'); ALTER TABLE [dbo].['+ @factTableName +'] CHECK CONSTRAINT ['+ @factTableName +'_Chk]'
print @chkScript
if @fixFactDiscrepancies = 1
begin try
exec sp_executesql @chkScript
set @factCountCorrected = @factCountCorrected +1
end try
begin catch
print 'Error happened with: ' + @chkScript
print ERROR_MESSAGE()
end catch
end
--2. check in vw
set @selectClause = ''
select @selectClause=@selectClause+','+name from sys.columns where object_id=(select object_id from sys.views where name=@factName+'vw') order by column_id
set @selectClause = 'SELECT ' + SUBSTRING(@selectClause,2,8000) + ' FROM ' + @factTableName
if @isFirstIteration = 0
set @correctView = @correctView + ' UNION ALL '
set @correctView = @correctView + @selectClause
if CHARINDEX(@factTableName, @currentView)=0
begin
set @isViewCorrect=0
print @factTableName +' not in view.'
end
if @isFirstIteration =1 set @isFirstIteration=0
end; close c3; deallocate c3;
if @isViewCorrect=0
begin
declare @dropView nvarchar(max)
select @dropView='DROP VIEW [dbo].['+ @factName+'vw' +']'
if @fixFactDiscrepancies = 1
begin try
exec sp_executesql @dropView
end try
begin catch
print 'Error happened with: ' + @dropView
print ERROR_MESSAGE()
end catch
else
print @dropView
set @correctView = 'CREATE VIEW [dbo].['+ @factName+'vw' +'] AS '+@correctView
if @fixFactDiscrepancies = 1
begin try
exec sp_executesql @correctView
set @factViewCountCorrected = @factViewCountCorrected +1
end try
begin catch
print 'Error happened with: ' + @correctView
print ERROR_MESSAGE()
end catch
else
print @correctView
end
end; close c; deallocate c;
declare @result nvarchar(max)
print ''
print '*****************************************************************'
print '----- R E S U L T for database: ' + DB_NAME()
print '*****************************************************************'
print ''
if @dec31Exists=0 or @dateCountAfter2021 < 10956
begin
if @fixFactDiscrepancies = 0
print 'Issues in DateDim found. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
else
print 'Issues in DateDim found. They have been all corrected. Run the script again for verification.'
end
else
print 'No issues found in DateDim.'
-------------------
print ''
-------------------
if @factCountCorrected > 0
begin
set @result = cast(@factCountCorrected as nvarchar(max)) + ' of ' + cast(@factCountTotal as nvarchar(max)) + ' CONSTRAINTS in FACT tables are incorrect.'
print @result
if @fixFactDiscrepancies = 0
print 'They have NOT been corrected. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
else
print 'They have been all corrected. Run the script again for verification.'
end
else
print 'No CONSTRAINT discrepancies found in ' + cast(@factCountTotal as nvarchar(max)) + ' FACT tables.'
-------------------
print ''
-------------------
if @factViewCountCorrected > 0
begin
set @result = cast(@factViewCountCorrected as nvarchar(max)) + ' of ' + cast(@factViewCountTotal as nvarchar(max)) + ' VIEW definitions are incorrect.'
print @result
if @fixFactDiscrepancies = 0
print 'They have NOT been corrected. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
else
print 'They have been all corrected. Run the script again for verification.'
end
else
print 'No discrepancies found in ' + cast(@factViewCountTotal as nvarchar(max)) + ' FACT views.'