Checking All Date Columns in a Table(s)

,

Summary:  An ETL process which moved data from a new application to a legacy one, encountered errors in date columns.  Since there were multiple data types and insufficient validation by the user application, a quick check of numerous date columns was necessary.  This stored procedure provides a quick scan of all columns in a table or range of tables, displaying the minimum and maximum values for all Date, DateTime, and SmallDateTime columns.

Detail:  A new application for tracking IT projects used a DATE datatype for the numerous dates recorded in any given project.  Unfortunately, since DATE allows values from "0001-01-01 through 9999-12-31" (http://msdn.microsoft.com/en-us/library/bb630352.aspx ) and the user application did no validation, a mistyped date caused issues for an SSIS process that moved data to legacy tables in which som columns were datatype SmallDateTime.  This datatype will only accept values from "1900-01-01 through 2079-06-06" (http://msdn.microsoft.com/en-us/library/ms182418.aspx )

Since the error can be the results of values in several dozen columns, a quick snapshot of minimum and maximum values seemed useful.  This stored procedure provides that quick inspection.  Here is an example of its usage - note that one of the columns will pose a problem:

/****** Object:  StoredProcedure [dbo].[usp_TblDateMinMax]    Script Date: 10/27/2014 9:02:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =====================================================
-- Author:		Larry Schmidt
-- Create date: October 21, 2014
-- Description:	Find the maximum and minimum dates
--                 in each of the date columns in
--				   all Scorecard tables SCD_Projectxxx
-- =====================================================
CREATE PROCEDURE [dbo].[usp_TblDateMinMax] @TableMask varchar(max)

AS

CREATE Table #DateResults (ID int,TblCol varchar(max), Maxdt date, Mindt date)

select IDENTITY(INT, 1,1) AS [ID],o.name AS Tbl,c.name AS Col
  into #Scorecardtmp
from syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
where o.xtype = 'U' and c.xusertype in (40,58,61) and o.name like @TableMask and c.name <> 'RowInsertDate' and c.name <> 'LastModifiedDate'
order by o.name,c.name

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(ID) FROM #Scorecardtmp)

DECLARE @Iter INT
SET @Iter = 1

DECLARE @SQLCmd varchar(max), @Maxdt date, @Mindt date, @Tbl varchar(max), @Col varchar(max), @ID int

 WHILE @Iter <= @MaxRownum
BEGIN
     SELECT @ID = ID, @Tbl = Tbl, @Col = Col from #Scorecardtmp WHERE ID = @Iter
	 INSERT INTO #DateResults (ID, TblCol) VALUES (@Iter,'Table: ' + @Tbl + ', Column: ' + @Col)
	 SET @SQLCmd = 'UPDATE #DateResults SET Mindt = (SELECT min(' + @Col + ') from ' + @Tbl + ') where ID = ' + CAST(@Iter as varchar)
	 exec (@SQLCmd)
	 SET @SQLCmd = 'UPDATE #DateResults SET Maxdt = (SELECT max(' + @Col + ') from ' + @Tbl + ') where ID = ' + CAST(@Iter as varchar)
     exec (@SQLCmd)
     
     SET @Iter = @Iter + 1
END

select * from #DateResults

drop table #Scorecardtmp
drop table #DateResults

Rate

3.86 (7)

Share

Share

Rate

3.86 (7)