How To Get List Of SQL SPs/Views/Functions/Triggers Containing Certain Word/Pattern

Sometimes you find yourself in a situation where you need to know where a certain word (which may be a name for a column or just a string message) exists in your SQL database code items like stored procedures or views or functions or triggers.

In such situation, you will need a search feature which can aid you to find the word you are looking for.

I know that you are not supposed to face such case because there should be a proper sort of documentation for the database design. But, believe me in some cases this type of documentation is not found and you are asked to work on the database to fix some bugs or whatever.

So, to do your job, you may need to find a certain word in the database. Why? because this word may be a key word for your whole issue.

To imagine what I am talking about here, let's have a look on these cases:

• Want to know where a certain column in a table is updated
• Want to know where a certain stored procedure is called
• Want to know where data is inserted into a certain table
• Want to know where a certain table is used to retrieve data from
• Want to know where a certain view is used to retrieve data from

These are just some examples and there are many others. So, to carry out these tasks, you need to search the whole database items for a word or a pattern to find the database items which include this word or pattern into its code definition. Then, you can work on the analysis of the results you got.


So, I wrote a SQL script which carries out this search functionality. The script is as below:

 use [DbName] --Database name
GO

DECLARE @SearchText varchar(max)
SET @SearchText='KeyWord' --The (word)/(like pattern) to search for

SELECT DISTINCT
'[' + s.[name] + '].[' + obj.name + ']' AS [ObjectName]
, obj.type_desc AS [Object Type]
, module.definition AS [Code]
, obj.create_date AS [Date Created]
, obj.modify_date AS [Date Modified]

FROM
sys.sql_modules module

INNER JOIN sys.objects obj
ON module.object_id = obj.object_id

INNER JOIN sys.schemas s
ON s.schema_id = obj.schema_id

WHERE module.definition Like '%' + @SearchText + '%'
   
GROUP BY obj.type_desc
, '[' + s.[name] + '].[' + obj.name + ']'
, module.definition
, obj.create_date
, obj.modify_date

ORDER BY obj.type_desc
, '[' + s.[name] + '].[' + obj.name + ']'
, module.definition
, obj.create_date
, obj.modify_date

To use the script, you just need to update the database name in the "use" statement (first line) and the word or the like pattern you wish to search for (fifth line).


Wish this helps you someday :)