MSSQL 2005 - Is there a way to find out what views (definition, not data content) have been modified and when ?

javamahn
javamahn used Ask the Experts™
on
Over the course of the last few months, I have made several changes (altered) to views used by the application we are installing.  I have added fields to many views for reporting purposes and also a few stored procedures.

We are scheduled for an application upgrade In a few days and I have been told the view definitions will be overwritten.  So now my problem, How do i find all the views and stored procedures that I have modified so that I can get a copy of them and restore it after the upgrade?

If i could see something like a last modified date ( applying to the view definition and not the last row insertion), i could easily find them instead of manually looking at 1000 views and stored procedures for my comments.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:

if you need additional columns include * in result set.
SELECT name, modify_date  
FROM sys.objects
WHERE type IN ('U', 'V')

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:

if you need additional columns include * in result set.
SELECT name, modify_date  
FROM sys.objects
WHERE type IN ('U', 'V')

Open in new window

Awarded 2008
Awarded 2008

Commented:
Hmmm...you can if you've set DDL triggers...otherwise, you really can't unless you get a 3rd party tool.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
For stored procedures and Functions include 'P' and 'FN' in the list.

Author

Commented:
Perfect, Exactly what i was looking for.  This will save me hours.
Thanks again.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome and glad to help you out..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial