Search latest modified SP and View with its text in SQL Server 2008/2005

Wednesday, September 23, 2009 |

Today I have seen interesting question in one forum that how can I find modified Stored Procedure and Views after certain date with its text. Question is really interesting but the answer is really very small T-SQL.
If you use Sys.Objects, you can get list of all available objects in your database. Sys.Object has much important information about that object along with “Modify_Date” but this system view doesn’t contain text of those objects.

Well, in this scenario Object_Defination method comes to rescue us. If you pass Object’s ID in Object_Defination method, it will return Text for that object. So now let me show you short T-SQL for this task.

select name,OBJECT_DEFINITION(object_id),modify_date from sys.objects
where [type] in ('V','p')  and convert(varchar,modify_date,112)>'20090601'

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of


Madhivanan said...

You dont need to convert the date to varchar in the WHERE clause

Ritesh Shah said...

yes, you are right. it will work without converting date too