Thursday, August 27, 2015

Search Text in schema of Stored Procedure

With a big database this is one of the basic issue if you want to search whether a particular string/value available in anywhere in schema of stored procedure.
So we cant do it manually (yeah we have option of looking into dependencies but this is one step ahead) like It will help us to search in even comments too.
 

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%This part is commented%'
GO


Happy Living...
Happy Coding...
Happy Concepts ...