• Nice script and great tip on doing the left join to get table info.

    SELECT

    DISTINCT

    TypeDescription,

    SchemaName,

    Name,

    '...' + SUBSTRING(t.ObjectDefinition,

    CHARINDEX(@chvStringToFind,

    t.ObjectDefinition)

    - @intNbCharToExtract,

    LEN(@chvStringToFind)

    + ( @intNbCharToExtract * 2 )) + '...' AS Extract,

    CreationDate,

    ModificationDate

    FROM (

    SELECT DISTINCT

    o.name AS Name,

    SCHEMA_NAME (o.schema_id) AS SchemaName,

    o.type_desc AS TypeDescription,

    o.create_date AS CreationDate,

    o.modify_date AS ModificationDate,

    ISNULL(OBJECT_DEFINITION(object_id), c.name) AS ObjectDefinition

    FROM sys.objects o WITH (NOLOCK)

    LEFT OUTER JOIN syscolumns c

    ON c.id = o.object_id

    WHERE

    --(

    --( o.type IN ( 'AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X' )

    --AND @chrObjectType IS NULL

    --)

    --OR o.type = @chrObjectType

    --)

    (o.type = @chrObjectType OR @chrObjectType IS NULL)

    AND (OBJECT_DEFINITION(o.object_id) LIKE '%' + @chvStringToFind + '%'

    OR

    c.name LIKE '%' + @chvStringToFind + '%'

    )

    ) AS t

    ORDER BY

    1,

    2,

    3,

    4,

    5,

    6