<p>How can I find out the date a MS SQL Server 2000 object was last modified?</p>

<p>I need to get a list of all the views, procs, functions etc that were modified since Aug 15th.  In sysObjects I can see the date objects were created but I need to know when they were last altered.</p>

<p>NB: this is an SQL 2000 database.</p>

[{'answer_id': 78628, 'author': 'Portman', 'author_id': 1690, 'author_profile': 'https://Stackoverflow.com/users/1690', 'pm_score': 4, 'selected': True, 'text': '<p>Note that SQL Server actually does <strong>not</strong> record the last modification date. It does not exist in any system tables. </p>\n\n<p>The Schema Changes History report is actually constructed from the <a href="http://msdn.microsoft.com/en-us/library/ms191006.aspx" rel="noreferrer">Default Trace</a>. Since many admins (and web hosts) turn that off, it may not work for you.\nBuck Woody had a good explanation of how this report works <a href="http://blogs.msdn.com/buckwoody/archive/2007/10/12/sql-server-management-studio-standard-reports-schema-changes-history.aspx" rel="noreferrer">here</a>. The data is also temporary.</p>\n\n<p>For this reason, you should never RELY on the Schema Changes History report. Alternatives:</p>\n\n<ul>\n<li>Use <a href="http://msdn.microsoft.com/en-us/library/ms186406.aspx" rel="noreferrer">DDL Triggers</a> to log all schema modification to a table of your choosing.</li>\n<li>Enforce a protocol where views and procs are never altered, they are only dropped and recreated. This means the created date will also be the last updated date (this does not work with tables obviously). </li>\n<li>Vigilantly version your SQL objects and schema in source control. </li>\n</ul>\n\n<p>--</p>\n\n<p>Edit: saw that this is SQL 2000. That rules out Default Trace and DDL Triggers. You\'re left with one of the more tedious options I listed above.</p>\n'}, {'answer_id': 2342967, 'author': 'Allov', 'author_id': 130480, 'author_profile': 'https://Stackoverflow.com/users/130480', 'pm_score': 4, 'selected': False, 'text': '<p>I know this is a bit old, but it is possible to view the last altered date of <strong>stored procs and functions</strong> with this query:</p>\n\n<pre><code>USE [Your_DB]    \nSELECT * FROM INFORMATION_SCHEMA.ROUTINES\n</code></pre>\n\n<p>Just thought it would be nice to mention this since I searched for this very solution and this thread was misleading.</p>\n'}, {'answer_id': 22472528, 'author': 'Sudhir v. Usnale', 'author_id': 3431963, 'author_profile': 'https://Stackoverflow.com/users/3431963', 'pm_score': 3, 'selected': False, 'text': "<p>I have got confirmed Answer for above any procedure History for modified date with below query </p>\n\n<p>Step -1 Execute the procedure on DB</p>\n\n<pre><code>SELECT name, create_date, modify_date \nFROM sys.objects\nWHERE type = 'p' \n</code></pre>\n\n<p>Step -2 \nThen copy the text to Excel with headers</p>\n\n<p>select route coloumn and then paste the exact procedure name into ^F window and press enter \nyou will get exact modified date.</p>\n\n<p>Regards,\nSudhir \nPune</p>\n"}, {'answer_id': 30715530, 'author': 'Saurabh Sinha', 'author_id': 3252237, 'author_profile': 'https://Stackoverflow.com/users/3252237', 'pm_score': 1, 'selected': False, 'text': '<p>This is not always correct because modify_date is Date the object was last modified by using an ALTER statement. <strong>If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.</strong></p>\n'}]
['https://Stackoverflow.com/questions/78471', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/10422/']