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