Finding text in SQL objects definitions
Includes stored procedures and triggers across all databases on a server.
select s.name schema_name, o.name object_name, o.type_desc, po.name parent_name 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 left join sys.objects po on o.parent_object_id = po.object_id where m.definition like '%ENTER_YOUR_TEXT_HERE%' order by 2, 1
For all databases on a server, use this:
exec sp_MSforeachdb 'select ''?'', s.name schema_name, o.name object_name, o.type_desc, po.name parent_name 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 left join sys.objects po on o.parent_object_id = po.object_id where m.definition like ''%ENTER_YOUR_TEXT_HERE%'' order by 2, 1'
Finding text in SQL Server Agent jobs
SELECT Job.name, JobStep.step_name FROM msdb.dbo.sysjobs Job INNER JOIN msdb.dbo.sysjobsteps JobStep ON Job.job_id = JobStep.job_id WHERE JobStep.command LIKE '%ENTER_YOUR_TEXT_HERE%'
Table hints and NOLOCK
Table hints are statements which instruct SQL to use specific locking methods or indexes when optimizing a query. One of the most common table hints is NOLOCK (or, its equivalent, READUNCOMMITTED).
Consider the following description from MSDN:
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).
NOLOCK is a handy way to speed up queries when performance is blocked due to excessive locking on a table. This can happen on a table that has a high number of INSERTs, UPDATEs, and DELETEs. However, you should not use NOLOCK — or any other table hint — unless an issue is already known to exist, and the table hint is used to explicitly resolve the issue without creating new issues.
In other words, do not add NOLOCK unless it is known to address a specific and known problem in a query.
(No premature optimization!)