I thought this would be worth sharing, in its current form. It’s a small piece of SQL (this would be encapsulated in a Stored Procedure) that allows you to search through a host of objects encompassing Stored Procedures, Functions, Views, Triggers and even Indexes for a specific search term (i.e. an object name).
I’ve utilised a CTE here (just for readability more than anything; a small sampling of actual Execution Plans suggest this isn’t hurting performance to structure the code in this way) to pull through the full object definition, for any matches, where types easily support it. Indexes are the tricky ones to deal with here; from what I’ve gathered the definition would need to be manually constructed; hence, I’ve omitted generating a definition for now.
This is essentially a bit of an extension of some rougher and readier code put together in my SQL Debugging Tips – Part Two post:
The code is as follows (would be appropriately wrapped in a Stored Procedure):
SET NOCOUNT ON; --1) Validation - Ensure that a valid Search Term has been supplied (cannot be NULL or an empty string) IF (COALESCE(@SEARCH_TERM, '') = '') BEGIN RAISERROR('@SEARCH_TERM cannot be an empty string or NULL. Please specify a valid value (procedure: YOUR_PROCEDURE_NAME).', 16, 1); RETURN; --Input invalid, simply return END; --Enable a 'fuzzy' match SET @SEARCH_TERM = '%' + @SEARCH_TERM + '%'; --CTE that is used to 'bundle' up all of the various objects being search (for the given Search Term) WITH COMBINED_OBJECTS ( OBJ_ID , OBJ_NAME , OBJ_TYPE , OBJ_DEFINITION ) AS ( --Search Procedures, Functions, Triggers and Views for the Search Term (in the actual definition of the object) SELECT sm.object_id AS [OBJ_ID] , so.name AS [OBJ_NAME] , so.type_desc AS [OBJ_TYPE] , sm.definition AS [OBJ_DEFINITION] FROM sys.sql_modules sm INNER JOIN sys.objects so ON sm.object_id = so.object_id WHERE sm.definition LIKE @SEARCH_TERM UNION ALL --Search for the Search Term in the name of an index SELECT si.object_id AS [OBJ_ID] , si.name AS [OBJ_NAME] , 'INDEX - ' + si.type_desc COLLATE DATABASE_DEFAULT AS [OBJ_TYPE] --Negate collation issues with concatenation , NULL AS [OBJ_DEFINITION] FROM sys.indexes si WHERE si.name LIKE @SEARCH_TERM UNION ALL --Search for the Search Term in the physical column names that comprise an index definition SELECT si.object_id AS [OBJ_ID] , sc.name + ' (' + si.name COLLATE DATABASE_DEFAULT + ' - ' + si.type_desc COLLATE DATABASE_DEFAULT + ')' AS [OBJ_NAME] --Negate collation issues with concatenation , 'INDEX_COLUMN' AS [OBJ_TYPE] , NULL AS [OBJ_DEFINITION] FROM sys.indexes si INNER JOIN sys.index_columns sic ON ( si.object_id = sic.object_id AND si.index_id = sic.index_id ) INNER JOIN sys.columns sc ON ( sic.object_id = sc.object_id AND sic.column_id = sc.column_id ) WHERE sc.name LIKE @SEARCH_TERM ) --Return the results to the caller (can be expanded as needed) SELECT co.OBJ_ID , co.OBJ_NAME , co.OBJ_TYPE , co.OBJ_DEFINITION FROM COMBINED_OBJECTS co ORDER BY co.OBJ_TYPE, co.OBJ_NAME; --Do a little bit of ordering to make the results easier to digest
With Indexes, I’ve allowed the ability for a developer to search within the name of the Index and search for hits linked to the columns which comprise the Index.
This is all based on a ‘fuzzy’ search (aka using Wild Cards) – @SEARCH_TERM would be an input parameter of the Stored Procedure of type NVARCHAR. A slight spin on a basic check on sys.procedures and a little more expansive.
Just a small post to get this off my chest.
7 thoughts on “SQL – More Expansive Object Searching”
quite useful…i used to write sprocs traverse all sql databases on a server looking for specific context within those databases and then when they are found enable update or delete to modify them.
A worthy challenge should you accept it 🙂
Input parameters are servername, sa details, search value, action and replacement value (optional)
LikeLiked by 1 person
Sounds like something I’ll have to give a go – Will earmark it for a future post. You’re the first to officially comment also which makes you awesome.
Pretty! This has been an incredibly wonderful article.
Many thanks for providing these details.
LikeLiked by 1 person
Hi there, glad you found this interesting. Thanks a bunch!
Great, glad to have helped.
This site was… how do I say it? Relevant!! Finally I have found something that helped me.
LikeLiked by 1 person
Awesome, pleased this came up trumps.