Programming, SQL Database

Find Text In MSSQL Database Stored Procedures, Functions And Views

In some case you have to find the reference or use of that text in the database included in tables, stored procedures, function, user defined types, views etc. In such case you have to find the name of tables and also refer every text of views, stored procedures or functions so here is the solution to find out references by using run below query with apply search text.

DECLARE @SearchText as nvarchar(256) = 'Participant'
SELECT DISTINCT o.name AS Object_Name,o.type_desc,m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%'+ @SearchText +'%'

In above query, you can search text in all objects of the database and you can easily find out type_desc which defines what type of object like the view, table, stored procedure, function etc. also from the definition you can get the whole definition of the object.

Tagged

Leave a Reply

Your email address will not be published. Required fields are marked *