Search In the Database using Text 1

Search In the Database using Text

declare @SearchText nvarchar(max)='searchText'
SELECT  o.[name] as object_name
                                                            , sm.[definition]
                                                    FROM sys.sql_modules AS sm
                                                    JOIN sys.objects AS o
                                                        ON sm.object_id = o.object_id
                                                    JOIN sys.schemas AS ss
                                                        ON o.schema_id = ss.schema_id
where sm.[definition] like '%'+@SearchText+'%'

Here is what the above code is Doing:
1. Declare a variable called @SearchText and set it to the value ‘searchText’
2. Select the object name and definition from the sys.sql_modules table
3. Join the sys.objects table to get the object name
4. Join the sys.schemas table to get the schema name
5. Filter the results to only include objects that contain the search text

Similar Posts