May 27, 2010 at 8:26 pm
Comments posted to this topic are about the item Find a string in database objects
June 21, 2010 at 5:17 am
I wasn't seeing any tables in my results. I think you need a left outer join to sys_columns because Object_Definition on Table
returns NULL
June 21, 2010 at 5:16 pm
I think mine works better:
http://www.sqlservercentral.com/scripts/Search/64839/
It hits all databases and includes job steps.
June 22, 2012 at 8:54 am
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
May 19, 2016 at 6:54 am
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply