October 26, 2010 at 3:49 am
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]
October 27, 2010 at 2:46 am
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]
October 27, 2010 at 3:36 am
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.
October 27, 2010 at 3:52 am
Thanks for reply Steveb.
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
October 27, 2010 at 4:10 am
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
October 27, 2010 at 4:19 am
Thanks Gail.
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
September 19, 2013 at 10:05 am
To get the Tables Altered, you can use
SELECT name, create_date, modify_date
FROM sys.objects
WHERE modify_date >= GETDATE()-n
September 19, 2013 at 10:35 am
davehegwood (9/19/2013)
To get the Tables Altered, you can useSELECT 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
February 3, 2018 at 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')
February 3, 2018 at 5:24 am
patelmaulesh007 - Saturday, February 3, 2018 4:54 AMSELECT 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
May 24, 2018 at 3:22 am
GilaMonster - Saturday, February 3, 2018 5:24 AMpatelmaulesh007 - Saturday, February 3, 2018 4:54 AMSELECT 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...
May 24, 2018 at 3:29 am
BOR15K - Thursday, May 24, 2018 3:22 AMGilaMonster - Saturday, February 3, 2018 5:24 AMpatelmaulesh007 - Saturday, February 3, 2018 4:54 AMSELECT 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
May 24, 2018 at 3:58 am
ThomasRushton - Thursday, May 24, 2018 3:29 AMBOR15K - Thursday, May 24, 2018 3:22 AMGilaMonster - Saturday, February 3, 2018 5:24 AMpatelmaulesh007 - Saturday, February 3, 2018 4:54 AMSELECT 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';
May 24, 2018 at 4:02 am
BOR15K - Thursday, May 24, 2018 3:58 AMThomasRushton - Thursday, May 24, 2018 3:29 AMBOR15K - Thursday, May 24, 2018 3:22 AMGilaMonster - Saturday, February 3, 2018 5:24 AMpatelmaulesh007 - Saturday, February 3, 2018 4:54 AMSELECT 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
May 24, 2018 at 4:13 am
Agree. I need those, which were amended.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply