How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days.

  • Pritam Salvi

    SSC Eights!

    Points: 978

    How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days.

    I have two databases which is Thomasnet_N other one one Thomasnet_New

    I want to find out which is recently updated which one i want to use for live testing.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • Pritam Salvi

    SSC Eights!

    Points: 978

    I have this query to find out created table in last N days.

    Select name, id, crdate

    From sysobjects

    Where type ='U'

    and DateDiff(D,refdate,getdate()) < 7;

    But it will get last created, I want last modified or insert/updated table.

    Is there any idea?

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • steveb.

    SSC-Forever

    Points: 46733

    you wont be able to get that information by default sorry, you can build this functionality in with triggers however this will only help for the future.

  • Pritam Salvi

    SSC Eights!

    Points: 978

    Thanks for reply Steveb.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    You'll need a trace to get the DDL (schema) changes as SQL 2000 has no DDL triggers.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Pritam Salvi

    SSC Eights!

    Points: 978

    Thanks Gail.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • davehegwood

    SSC Journeyman

    Points: 75

    To get the Tables Altered, you can use

    SELECT name, create_date, modify_date

    FROM sys.objects

    WHERE modify_date >= GETDATE()-n

  • Gail Shaw

    SSC Guru

    Points: 1004424

    davehegwood (9/19/2013)


    To get the Tables Altered, you can use

    SELECT name, create_date, modify_date

    FROM sys.objects

    WHERE modify_date >= GETDATE()-n

    Won't work on SQL 2000.

    3 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • patelmaulesh007

    SSC Rookie

    Points: 41

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

  • Gail Shaw

    SSC Guru

    Points: 1004424

    patelmaulesh007 - Saturday, February 3, 2018 4:54 AM

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

    Won't work on SQL 2000.

    8 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • BOR15K

    SSCertifiable

    Points: 5752

    GilaMonster - Saturday, February 3, 2018 5:24 AM

    patelmaulesh007 - Saturday, February 3, 2018 4:54 AM

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

    Won't work on SQL 2000.

    8 year old thread.

    I thought sys.dm_db_index_usage_stats is only to trace DML changes. How do you find DDL ones? I have a request (SQL 2014) to provide a list of all the tables, changed since January this year - their columns, indices etc. but NOT
    the data inserted, modified or deleted...

  • ThomasRushton

    SSC-Insane

    Points: 22595

    BOR15K - Thursday, May 24, 2018 3:22 AM

    GilaMonster - Saturday, February 3, 2018 5:24 AM

    patelmaulesh007 - Saturday, February 3, 2018 4:54 AM

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

    Won't work on SQL 2000.

    8 year old thread.

    I thought sys.dm_db_index_usage_stats is only to trace DML changes. How do you find DDL ones? I have a request (SQL 2014) to provide a list of all the tables, changed since January this year - their columns, indices etc. but NOT
    the data inserted, modified or deleted...

    Still an 8 year old thread...

    By "all tables changed" - do you mean Schema changes?  If so, just get a backup from January, and one from today, and use something like SQL Compare (free trial available) to generate a list of changes...

    ...but that's not going to help the OP...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • BOR15K

    SSCertifiable

    Points: 5752

    ThomasRushton - Thursday, May 24, 2018 3:29 AM

    BOR15K - Thursday, May 24, 2018 3:22 AM

    GilaMonster - Saturday, February 3, 2018 5:24 AM

    patelmaulesh007 - Saturday, February 3, 2018 4:54 AM

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

    Won't work on SQL 2000.

    8 year old thread.

    I thought sys.dm_db_index_usage_stats is only to trace DML changes. How do you find DDL ones? I have a request (SQL 2014) to provide a list of all the tables, changed since January this year - their columns, indices etc. but NOT
    the data inserted, modified or deleted...

    Still an 8 year old thread...

    By "all tables changed" - do you mean Schema changes?  If so, just get a backup from January, and one from today, and use something like SQL Compare (free trial available) to generate a list of changes...

    ...but that's not going to help the OP...

    Something more practical , like the following in Oracle?

    SELECT OBJECT_NAME Table_Name,
    TIMESTAMP LAST_DDL
    FROM sys.all_objects
    WHERE object_name='myTable';

  • ThomasRushton

    SSC-Insane

    Points: 22595

    BOR15K - Thursday, May 24, 2018 3:58 AM

    ThomasRushton - Thursday, May 24, 2018 3:29 AM

    BOR15K - Thursday, May 24, 2018 3:22 AM

    GilaMonster - Saturday, February 3, 2018 5:24 AM

    patelmaulesh007 - Saturday, February 3, 2018 4:54 AM

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'TreatmentFADT')
    AND OBJECT_ID=OBJECT_ID('CommercialTeams')

    Won't work on SQL 2000.

    8 year old thread.

    I thought sys.dm_db_index_usage_stats is only to trace DML changes. How do you find DDL ones? I have a request (SQL 2014) to provide a list of all the tables, changed since January this year - their columns, indices etc. but NOT
    the data inserted, modified or deleted...

    Still an 8 year old thread...

    By "all tables changed" - do you mean Schema changes?  If so, just get a backup from January, and one from today, and use something like SQL Compare (free trial available) to generate a list of changes...

    ...but that's not going to help the OP...

    Something more practical , like the following in Oracle?

    SELECT OBJECT_NAME Table_Name,
    TIMESTAMP LAST_DDL
    FROM sys.all_objects
    WHERE object_name='myTable';

    That won't tell you which objects have been dropped...

    ...and neither will tell you which objects have been created & dropped in the meantime.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • BOR15K

    SSCertifiable

    Points: 5752

    Agree. I need those, which were amended.

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

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