|
|
You're in the
sql server programming
group:How to know the date of the last update of a table?
sql server programming:
in sql 2k, one has to add a column to keep this info, But sql 2005 surely keeps such info (date of the last update of a table) in its catalog. but where? Thanks
This information is not stored automatically, even in SQL 2005.
-- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com [quoted text, click to view] "newToSql" <noneone@hotmail.com> wrote in message news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... > Hi, > in sql 2k, one has to add a column to keep this info, But sql 2005 surely > keeps such info (date of the last update of a table) in its catalog. > but where? > Thanks > >
use getdate() in sql server to insert the date and time of the update into a
table column update MyTableName set MyColumnName = getdate() where xxxxxxxxxxx regards Tore
Hi,
In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data management view, look at the column last_user_update, you can also see when the table was last accessed (last_user_seek and last_user_scan - do a MAX on them). select * from sys.dm_db_index_usage_stats where database_id = db_id( 'readpasttest' ) Note, this view is reset when SQL Server restarts so is only current since SQL last started, but I suppose you could write some logic to capture the value but it would be slightly out of date because you can't grab the event when SQL Server is shutting down. Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "newToSql" <noneone@hotmail.com> wrote in message
news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... > Hi, > in sql 2k, one has to add a column to keep this info, But sql 2005 surely > keeps such info (date of the last update of a table) in its catalog. > but where? > Thanks > >
Hello,
In SQL 2005 also you may need to create a new column to know when the table was last updated. In SQL 2005 Schema changes can be audited using DDL triggers. THanks Hari [quoted text, click to view] "newToSql" <noneone@hotmail.com> wrote in message news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... > Hi, > in sql 2k, one has to add a column to keep this info, But sql 2005 surely > keeps such info (date of the last update of a table) in its catalog. > but where? > Thanks > >
This is very helpful.Thank you so much.
[quoted text, click to view] "Tony Rogerson" <tonyrogerson@torver.net> wrote in message news:%23zlqMqdpHHA.4188@TK2MSFTNGP02.phx.gbl... > Hi, > > In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data > management view, look at the column last_user_update, you can also see > when the table was last accessed (last_user_seek and last_user_scan - do a > MAX on them). > > select * > from sys.dm_db_index_usage_stats > where database_id = db_id( 'readpasttest' ) > > Note, this view is reset when SQL Server restarts so is only current since > SQL last started, but I suppose you could write some logic to capture the > value but it would be slightly out of date because you can't grab the > event when SQL Server is shutting down. > > Tony. > > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > > > "newToSql" <noneone@hotmail.com> wrote in message > news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... >> Hi, >> in sql 2k, one has to add a column to keep this info, But sql 2005 surely >> keeps such info (date of the last update of a table) in its catalog. >> but where? >> Thanks >> >> >
I just had never used that view for this purpose, but it seems reasonable to
me. Give it a try. This wouldn't be the first thing I'd learned from Tony. :-) -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com [quoted text, click to view] "TheSQLGuru" <kgboles@earthlink.net> wrote in message news:ORqtSuxpHHA.4120@TK2MSFTNGP06.phx.gbl... > Is Tony's suggestion to use sys.dm_db_index_usage_stats invalid? > > -- > TheSQLGuru > President > Indicium Resources, Inc. > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:OGvAosYpHHA.4532@TK2MSFTNGP06.phx.gbl... >> This information is not stored automatically, even in SQL 2005. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> http://sqlblog.com >> >> >> "newToSql" <noneone@hotmail.com> wrote in message >> news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> in sql 2k, one has to add a column to keep this info, But sql 2005 >>> surely keeps such info (date of the last update of a table) in its >>> catalog. >>> but where? >>> Thanks >>> >>> >> >> > >
Is Tony's suggestion to use sys.dm_db_index_usage_stats invalid?
-- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:OGvAosYpHHA.4532@TK2MSFTNGP06.phx.gbl... > This information is not stored automatically, even in SQL 2005. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > http://sqlblog.com > > > "newToSql" <noneone@hotmail.com> wrote in message > news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... >> Hi, >> in sql 2k, one has to add a column to keep this info, But sql 2005 surely >> keeps such info (date of the last update of a table) in its catalog. >> but where? >> Thanks >> >> > >
Thanks Kalen, that is high praise.
But I don't think drinking large quantities of beer quickly counts ;) Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:%23QaDh9xpHHA.716@TK2MSFTNGP05.phx.gbl... >I just had never used that view for this purpose, but it seems reasonable >to me. Give it a try. > This wouldn't be the first thing I'd learned from Tony. > :-) > > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > http://sqlblog.com > > > "TheSQLGuru" <kgboles@earthlink.net> wrote in message > news:ORqtSuxpHHA.4120@TK2MSFTNGP06.phx.gbl... >> Is Tony's suggestion to use sys.dm_db_index_usage_stats invalid? >> >> -- >> TheSQLGuru >> President >> Indicium Resources, Inc. >> >> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >> news:OGvAosYpHHA.4532@TK2MSFTNGP06.phx.gbl... >>> This information is not stored automatically, even in SQL 2005. >>> >>> -- >>> HTH >>> Kalen Delaney, SQL Server MVP >>> www.InsideSQLServer.com >>> http://sqlblog.com >>> >>> >>> "newToSql" <noneone@hotmail.com> wrote in message >>> news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... >>>> Hi, >>>> in sql 2k, one has to add a column to keep this info, But sql 2005 >>>> surely keeps such info (date of the last update of a table) in its >>>> catalog. >>>> but where? >>>> Thanks >>>> >>>> >>> >>> >> >> > >
:-)
But that's something I'll never do as well as you. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com [quoted text, click to view] "Tony Rogerson" <tonyrogerson@torver.net> wrote in message news:%23ahSm%236pHHA.4772@TK2MSFTNGP05.phx.gbl... > Thanks Kalen, that is high praise. > > But I don't think drinking large quantities of beer quickly counts ;) > > Tony. > > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:%23QaDh9xpHHA.716@TK2MSFTNGP05.phx.gbl... >>I just had never used that view for this purpose, but it seems reasonable >>to me. Give it a try. >> This wouldn't be the first thing I'd learned from Tony. >> :-) >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> http://sqlblog.com >> >> >> "TheSQLGuru" <kgboles@earthlink.net> wrote in message >> news:ORqtSuxpHHA.4120@TK2MSFTNGP06.phx.gbl... >>> Is Tony's suggestion to use sys.dm_db_index_usage_stats invalid? >>> >>> -- >>> TheSQLGuru >>> President >>> Indicium Resources, Inc. >>> >>> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >>> news:OGvAosYpHHA.4532@TK2MSFTNGP06.phx.gbl... >>>> This information is not stored automatically, even in SQL 2005. >>>> >>>> -- >>>> HTH >>>> Kalen Delaney, SQL Server MVP >>>> www.InsideSQLServer.com >>>> http://sqlblog.com >>>> >>>> >>>> "newToSql" <noneone@hotmail.com> wrote in message >>>> news:eA8BcBYpHHA.4496@TK2MSFTNGP06.phx.gbl... >>>>> Hi, >>>>> in sql 2k, one has to add a column to keep this info, But sql 2005 >>>>> surely keeps such info (date of the last update of a table) in its >>>>> catalog. >>>>> but where? >>>>> Thanks >>>>> >>>>> >>>> >>>> >>> >>> >> >> >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |