Checking All Date Columns in a Tables

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.

if exists(select * from sys.objects where object_id = object_id(N'[dbo].[usp_TblDateMinMax]') and type in(N'P', N'PC'))
    begin
        drop procedure dbo.usp_TblDateMinMax
    end;
go

set ansi_nulls, 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
    begin
        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 as c
        inner join sysobjects as 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;
    end;
    
go