SQL – More Expansive Object Searching

Evening all,

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.

Cheers!

7 thoughts on “SQL – More Expansive Object Searching

  1. Mark says:

    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)

    Liked by 1 person

  2. Thanks Mark!

    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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s