Post reply

script to Find current date modify table list

  • June 10, 2015 at 3:20 pm

    #298818

    Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.

    Any help here pls.

    thanks in advance.

  • June 10, 2015 at 3:30 pm

    #1804525

    I have no idea what you're looking for. Can you reword your request?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 10, 2015 at 4:14 pm

    #1804539

    i need to find out the table names which are modified in last 24hrs.

  • June 10, 2015 at 7:47 pm

    #1804555

    charipg (6/10/2015)

    i need to find out the table names which are [font="Arial Black"]modified in last 24hrs[/font].

    SELECT * FROM sys.tables WHERE modify_date >= DATEADD(dd,-1,GETDATE());

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 11, 2015 at 1:43 pm

    #1804823

    thanks Jeff!!

  • June 11, 2015 at 1:48 pm

    #1804826

    charipg (6/10/2015)

    i need to find out the table names which are modified in last 24hrs.

    Jeff's answer is good if you are talking about schema changes not data changes.

  • June 11, 2015 at 2:30 pm

    #1804836

    you can kind of infer the last tiem a table was updated from the index stats, but you cannot tell what was updated, or by who.

    to have that, you need to put code in plac ethat specifically tracks that.

    --based on the ideas from

    --http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

    ;WITH ServerStarted AS

    (

    SELECT

    MIN(last_user_seek) AS first_seek,

    MIN(last_user_scan) AS first_scan,

    MIN(last_user_lookup) AS first_lookup

    FROM sys.dm_db_index_usage_stats

    ),

    ServerFirst AS

    (

    SELECT

    CASE

    WHEN first_seek < first_scan AND first_seek < first_lookup

    THEN first_seek

    WHEN first_scan < first_seek AND first_scan < first_lookup

    THEN first_scan

    ELSE first_lookup

    END AS usage_start_date

    FROM ServerStarted

    ),

    myCTE AS

    (

    SELECT

    DB_NAME(database_id) AS TheDatabase,

    OBJECT_NAME(object_id,database_id) As TheTableName,

    last_user_seek,

    last_user_scan,

    last_user_lookup,

    last_user_update

    FROM sys.dm_db_index_usage_stats

    )

    SELECT

    MIN(ServerFirst.usage_start_date) AS usage_start_date,

    x.TheDatabase,

    x.TheTableName,

    MAX(x.last_read) AS last_read,

    MAX(x.last_write) AS last_write

    FROM

    (

    SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE

    ) AS x

    CROSS JOIN ServerFirst

    WHERE 1= CASE WHEN DB_NAME() = 'master' THEN 1 WHEN TheDatabase = DB_NAME() THEN 1 ELSE 0 END

    AND last_write >= DATEADD(dd,-1,GETDATE())

    GROUP BY TheDatabase,TheTableName

    ORDER BY TheDatabase,TheTableName

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 11, 2015 at 4:57 pm

    #1804861

    Lynn Pettis (6/11/2015)

    charipg (6/10/2015)

    i need to find out the table names which are modified in last 24hrs.

    Jeff's answer is good if you are talking about schema changes not data changes.

    Correct. I was thinking schema changes, not data changes. Thanks for bringing up the difference.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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