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

September 23, 2009 — riteshshah
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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah
Posted in Microsoft, MS SQL Server 2005, ms sql server 2008, Ritesh Shah, search modified object, search text of Stored procedure. 2 Comments »

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

September 23, 2009 — riteshshah
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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah
Posted in Microsoft, MS SQL Server 2005, ms sql server 2008, OBJECT_DEFINITION, Ritesh Shah, search modified object. 2 Comments »