Thursday, February 23, 2012

How do I find the Database Objects (Stored Procedure, UDFs, Views) containing particular keywords?

by Ashish Upadhyay

Searching for a database objects is very much required if you are dealing with large databases which hundreds of stored procedures, views, functions and triggers, either based on some specific keywords or based on table or colummn name. Below queries will help you with that specially if you want to seach for the DB objects (lets say stored procedure) any keywords (a text, table name or column name) , these queries are written on top of system tables and system views.



SELECT DISTINCT SO.name, SO.xtype

FROM syscomments SC

INNER JOIN sysobjects SO ON SC.id = SO.id

WHERE SC.TEXT LIKE '%Search Keyword%'

ORDER BY SO.xtype





-- Query to find TEXT inside a stored procedure, view, functions and triggers

--This script will look into SQL as well as system objects.
SELECT OBJECT_NAME(M.object_id), O.type_desc
FROM sys.all_sql_modules M
INNER JOIN sys.all_objects O ON M.object_id = O.object_id
WHERE M.definition LIKE '%Search Keyword%'
ORDER BY O.type_desc



--This script will look into SQL objects only
SELECT OBJECT_NAME(M.object_id), O.type_desc
FROM sys.sql_modules M
INNER JOIN sys.all_objects O ON M.object_id = O.object_id
WHERE M.definition LIKE '%Search Keyword%'
ORDER BY O.type_desc



--This script will look into system objects only
SELECT OBJECT_NAME(M.object_id), O.type_desc
FROM sys.system_sql_modules M
INNER JOIN sys.all_objects O ON M.object_id = O.object_id
WHERE M.definition LIKE '%Search Keyword%'
ORDER BY O.type_desc


1 comment:

  1. I have used this in my project, where we were creating so many tables for testing purpose. So, to group them up, I used these queries.

    Very Useful post. Thanks Ashish.

    Siva Ram.

    ReplyDelete