Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find a string in database objects Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 8:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 2:00 AM
Points: 44, Visits: 38
Comments posted to this topic are about the item Find a string in database objects
Post #929416
Posted Monday, June 21, 2010 5:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:47 PM
Points: 24, Visits: 74
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
Post #940240
Posted Monday, June 21, 2010 5:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:38 AM
Points: 142, Visits: 287
I think mine works better:
http://www.sqlservercentral.com/scripts/Search/64839/

It hits all databases and includes job steps.
Post #940696
Posted Friday, June 22, 2012 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 6, Visits: 254
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

Post #1320008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse