sql2005

• eboucicaut

  Old Hand

  Points: 322

  More actions

  March 13, 2013 at 9:26 am

  #291049

  hello,

  how i can verify the last date modify of a table or a stored procedure.

  thank you for your help

  Boucicaut

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 13, 2013 at 9:35 am

  #1596587

  Look at the table sys.objects, it has the columns create_date and modify_date. This won't tell you the last table data in a table was updated or when a stored proc was run.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  March 13, 2013 at 9:37 am

  #1596589

  sys.objects has the last time the DDL of an object was modified.

  here's a simple example

  SELECT

  objz.create_date,

  objz.modify_date,

  SCHEMA_NAME(objz.schema_id) AS SchemaName,

  objz.name

  FROM sys.objects objz

  --WHERE objz.name = 'YourTableOrProcedure'

  ORDER BY objz.modify_date DESC

  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!

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  March 13, 2013 at 9:45 am

  #1596594

  For stored procedure you can check the column modify_date in sys.objects. Unfortunately with table it can be harder. If I remember correctly rebuild for the clustered index or the table modifies the value of column modify_date, so I'm not sure that you can see that without preparing something that logs all tables' modifications (for example DDL trigger).

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/