Scenario : Find the list of procedures which contains the text - "datafinder"
Solution : To get the procedure with text content we need to query two tables - sys.procedures and sys.Sql_modules.
The query to get all the procedure containing text - "datafinder" is below
SELECT [Name] FROM sys.procedures AS A
INNER JOIN sys.sql_modules AS B
ON A.object_id = B.object_id
AND B.definition like '%datafinder%'
Explanation : The sys.sql_modules keep the definition details of any sql module like view, procedure or function by object_id and sys.procedure contains the procedure with its object_id. Since we are specifically looking for procedure containing a specific text so we joined sys.sql_modules with sys.procedures. Similarly we can do the same for SQL Server views as shown below.
SELECT [Name],definition FROM sys.views AS A
INNER JOIN sys.sql_modules AS B
ON A.object_id = B.object_id
AND B.definition like '%datafinder%'
Or for function we can do as below
SELECT B.name,definition FROM sys.sql_modules AS A
INNER JOIN sys.SYSobjects AS B
ON A.object_id = B.id
WHERE B.type = 'FN'
AND B.definition like '%datafinder%'
SYSOBJECTS contains list of all system objects like function, table, procedures and views etc with different type column.
Comments
Post a Comment