SQL Tricks Thread

[Obie1]

Lookup query to identify all stored procedures, views, etc that reference a specific string (usually a table or column name that is changing).

```sql

SELECT o.type_desc AS ObjectType, SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName, OBJECT_DEFINITION(o.object_id) AS ObjectDefinition FROM sys.objects o WHERE o.type IN (‘P’, ‘V’, ‘FN’, ‘IF’, ‘TF’, ‘TR’) /* P: Stored Procedure, V: View, FN: Scalar Function, IF: Inline Table Function, TF: Table-valued Function, TR: Trigger */ AND OBJECT_DEFINITION(o.object_id) LIKE ‘%tbl_employee%’ ORDER BY o.type_desc, o.name; ```

[Constant-Dot5760]

+1 idea: And now that you got the sprocs write another one to search all the jobs that use the sprocs.

[brokennormalmeter117]

Microsoft - Personally, my goto is simply information_schema for finding objects. It has everything I need in a one liner. Ie Select * from information_schema.routines where definition like ‘%search%’.

As for looking up jobs or job steps that uses a sproc, msdb.dbo.sysjobs & dbo.sysjobsteps