Pages

Sunday 17 February 2013

SQL SERVER - Search text inside SQL

We can find a text or a string inside SQL Server stored procedures / triggers / views / functions using the below function:


CREATE FUNCTION [dbo].[SearchObject]
(
    @str VARCHAR(100)
)
RETURNS TABLE 
RETURN
    SELECT 
        DISTINCT 
        QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS ObjectFullName,
        s.name AS SchemaName,
        o.name AS ObjectName, 
        o.type_desc AS ObjectType
    FROM sys.objects o
    INNER JOIN syscomments c ON c.Id = o.object_id
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE 
    is_ms_shipped = 0 AND c.text like '%' + @str + '%'



SELECT * FROM [dbo].[SearchObject]('split')