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

Defining Downtime Down

Andy Mallon takes us through the notion of downtime: There’s a lot of discussion about preventing downtime. As a DBA and IT professional, it’s my sworn duty to prevent downtime. I usually describe my job as DBA something along the lines of, “to make sure data is always available to the people and applications that […]

Read More

Trying Out the Data Migration Assistant

Dave Mason shares some thoughts on the Data Migration Assistant: I recently took advantage of an opportunity to try Mirosoft’s Data Migration Assistant. It was a good experience and I found the tool quite useful. As the documentation tells us, the DMA “helps you upgrade to a modern data platform by detecting compatibility issues that can impact database […]

Read More

Categories

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