Search inside objects (Sprocs, FUnctions, etc)

I am currently using sys.syscomments to locate objects where a certain parameter exists. Is there another method / process for doing this that would make it easier to find the objects th开发者_运维技巧at contain that parameter or term?

Thanks,

S

It would be preferable to use the definition column of sys.sql_modules instead. sys.syscomments text is nvarchar(4000) so you can have issues with truncation when a definition splits across multiple rows.

select quotename(s.name)+'.'+quotename(o.name) as object_name, o.type_desc
    from sys.sql_modules m
        inner join sys.objects o 
            on m.object_id = o.object_id
        inner join sys.schemas s 
            on o.schema_id = s.schema_id
    where m.definition like '%YourSearchText%'

Also, if you're not doing this programmatically, Red Gate offers a free plug-in for SSMS called SQL Search; I'm not sure if there are other tools available.