SQL – Server Search

What do you do if you need to find a certain string used in a stored procedure? What if you need to be completely certain that you can remove the object and there are no dependencies within the server itself? There are a number of 3rd party tools that allow searching within the SQL Server database schemas, but some are slow due to precaching and some are simply not powerful enough due to lack of options. SQL itself comes to the rescue!

The query below can be as simple and as advanced as you want it to be, but no matter what- it is always fast and easy to use:

SELECT
OBJECT_NAME([id]) AS 'ObjectName',
MAX(CASE WHEN OBJECTPROPERTY([id], 'IsProcedure') = 1 THEN 'Procedure'
WHEN OBJECTPROPERTY([id], 'IsScalarFunction') = 1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY([id], 'IsTableFunction') = 1 THEN 'Table Function'
WHEN OBJECTPROPERTY([id], 'IsTrigger') = 1 THEN 'Trigger'
END) AS 'ObjectType'
FROM
[syscomments]
WHERE



LIKE '%SEARCHNEEDLE%'
AND (
OBJECTPROPERTY([id], 'IsProcedure') = 1
OR OBJECTPROPERTY([id], 'IsScalarFunction') = 1
OR OBJECTPROPERTY([id], 'IsTableFunction') = 1
OR OBJECTPROPERTY([id], 'IsTrigger') = 1
)
GROUP BY
OBJECT_NAME([id])
4/2/2012: Updated with more advanced SQL example