Checking All Date Columns in a Table(s)

  • Larry Schmidt-491187

    SSCrazy

    Points: 2553

    Comments posted to this topic are about the item Checking All Date Columns in a Table(s)

  • Romac

    Right there with Babe

    Points: 722

    Interesting idea Larry, thanks. Ques: would it be more efficient to do the MIN() and MAX() in one UPDATE?

  • Larry Schmidt-491187

    SSCrazy

    Points: 2553

    Perhaps so - I wanted to make the dynamic SQL UPDATES clear, and it does run pretty efficiently as is, but . . . always room for improvement!

    Thanks for the comment!

    Larry

  • ken.trock

    SSCertifiable

    Points: 5147

    Larry, I'm trying to do something similar in a user DB, get the minimum dates in their tables to see if there's an opportunity to implement a data retention policy.

    I had to add a provision to take schema into account. The updates fail if the table isn't in the default dbo schema.

    Thanks for coding this!

    Ken

    select IDENTITY(INT, 1,1) AS [ID],SCHEMA_NAME(t.schema_id) + '.' + t.name AS Tbl,c.name AS Col

    into #Scorecardtmp

    from syscolumns c

    /*Ken Trock 10/2015 - to get schema_id which is used in the min and max date updates. Will bomb otherwise if qualifying tables are not in dbo*/

    INNER JOIN sys.tables t ON c.id = t.object_id

    INNER JOIN sysobjects o ON o.id = c.id

    where o.xtype = 'U' and c.xusertype in (40,58,61) and t.name like @TableMask and c.name <> 'RowInsertDate' and c.name <> 'LastModifiedDate'

    order by t.name,c.name

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nifty script, thanks.

  • Sector7G

    Ten Centuries

    Points: 1040

    I'm doing a data quality project now to look for oddities. So this is useful for me.

    I modified the script so it includes schema, table, and column names in separate fields. That should make it easier to sort and find what you need. Also, I added [brackets] in case the table/column names contain spaces.

    SET NOCOUNT ON;

    DECLARE @TableMask VARCHAR(MAX)= '%multi%';

    IF OBJECT_ID('tempdb..#DateResults') IS NOT NULL

    DROP TABLE #DateResults;

    IF OBJECT_ID('tempdb..#Scorecardtmp') IS NOT NULL

    DROP TABLE #Scorecardtmp;

    CREATE TABLE #DateResults

    (ID INT,

    SchemaName varchar(255),

    TableName varchar(255),

    ColumnName varchar(255),

    Maxdt DATE,

    Mindt DATE

    );

    SELECT IDENTITY( INT, 1, 1) AS ID,

    s.Name AS SchemaName,

    o.name AS TableName,

    c.name AS ColumnName

    INTO #Scorecardtmp

    FROM syscolumns c

    INNER JOIN sysobjects o

    ON c.id = o.id

    inner join sys.schemas S

    on S.schema_id = O.uid

    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

    , @SchemaName varchar(max)

    , @TableName VARCHAR(MAX)

    , @ColumnName VARCHAR(MAX)

    , @ID INT;

    WHILE @Iter <= @MaxRownum

    BEGIN

    SELECT @ID = ID,

    @SchemaName = s.SchemaName,

    @TableName = s.TableName,

    @ColumnName = s.ColumnName

    FROM #Scorecardtmp s

    WHERE ID = @Iter;

    INSERT INTO #DateResults

    (ID,

    SchemaName,

    TableName,

    ColumnName

    )

    VALUES

    (@Iter,

    @SchemaName,

    @TableName,

    @ColumnName

    );

    SET @SQLCmd = 'UPDATE #DateResults SET Mindt = (SELECT min(['+@ColumnName+']) from ['+@SchemaName+'].['+@TableName+']) where ID = '+CAST(@Iter AS VARCHAR);

    EXEC (@SQLCmd);

    SET @SQLCmd = 'UPDATE #DateResults SET Maxdt = (SELECT max(['+@ColumnName+']) from ['+@SchemaName+'].['+@TableName+']) where ID = '+CAST(@Iter AS VARCHAR);

    EXEC (@SQLCmd);

    SET @Iter = @Iter + 1;

    END;

    SELECT

    dr.SchemaName

    ,dr.TableName

    ,dr.ColumnName

    ,dr.Mindt

    ,dr.Maxdt

    FROM #DateResults dr

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply