Databases and SQL

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!)