Finding Objects Using T-SQL

Derik Hammer shares a couple of snippets he uses to find objects and SQL Agent jobs.

Here’s one of my favorites, which searches for code within stored procedures, functions, and views:

SELECT
OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
OBJECT_NAME(sm.object_id) AS ObjectName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName,
definition
FROM sys.sql_modules sm
WHERE
sm.definition LIKE '%DEFINITION%'
--AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something'
--AND OBJECT_NAME(sm.object_id) = 'Something'
ORDER BY
SchemaName,
ObjectName;

Related Posts

Data File Migration With Minimal Downtime

Nate Johnson weaves a yarn around moving from one storage system to another with minimal downtime: Our ERP database has been chosen by the IT gods to get moved to the shiny new flash storage array, off the old spinning-rust SAN.  This is fantastic news for the business users.  But lo, the executives warn us, […]

Read More

Failed To Open Loopback Connection

Arun Sirpal diagnoses a connection problem: I could not read my error log on one of my local SQL Servers, when I executed the following code: EXEC sp_readerrorlog I received the below: Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State […]

Read More

Categories

November 2015
MTWTFSS
« Jan Dec »
 1
2345678
9101112131415
16171819202122
23242526272829
30