sql2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » sql2005

sql2005

Rate Topic

Display Mode

Topic Options

Author

Message

eboucicaut

Posted Wednesday, March 13, 2013 9:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:08 AM
Points: 36, Visits: 74

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

thank you for your help

Boucicaut

Post #1430474

Lynn Pettis

Posted Wednesday, March 13, 2013 9:35 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1430482

Lowell

Posted Wednesday, March 13, 2013 9:37 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1430484

Adi Cohn-120898

Posted Wednesday, March 13, 2013 9:45 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 2,019, Visits: 4,923

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/

Post #1430490

« Prev Topic | Next Topic »

Permissions