OK… this is not going to find your car keys… or the third cell phone you’ve lost in as many years… but it will find all the SQL code with the keywords you offer it. This is part three of the series on procs you should add to your shortcut menu. Find is Ctrl-4 in my hot key arsenal. View this post to learn how to add custom stored procedures to your shortcut options.

SAMPLE CALLS

Returns any code with the text ‘asset_id’ and any tables with exact column name matches:

^?View Code TSQL

1	find 'asset_id'

Locate any code with the text ‘asset_id’ and any tables with exact column name matches but code with ‘update’ will be eliminated:

^?View Code TSQL

1	find 'asset_id', @does_not_contain = 'update'

Special case. Only returns user tables with an exact column name match on ‘term_id’ and no code search. Fastest response time:

^?View Code TSQL

1	find 'asset_id',@obj_type = 'Table'

Code after the jump…

^?View Code TSQL

/* -----------------------------------------------------------------------------------------------
 * Procedure:  find
 *
 * DESCRIPTION: Search text of code and user table column names.
 * WRITTEN BY: James Bond @ http://www.007SQLMojo.com
 *
 * INPUT:	@search_text VARCHAR(255),
 *			@does_not_contain VARCHAR(255) = NULL,
 *			@obj_like VARCHAR(255) = '%',
 *			@obj_type VARCHAR(50) = '%'  
 *
 * OUTPUT: RECORDSET  
 *
 * SAMPLE CALLS: find 'asset_id' --> Returns any code with the text 'asset_id' and any tables with exact column name matches.
 *				 find 'asset_id', @does_not_contain = 'update'  --> any code with the text 'asset_id' and any tables 
 *					with exact column name matches but code with 'update' will be eliminated 
 *				 find 'asset_id',@obj_type = 'Table' --> Special case.  Only returns user tables with an exact column name
 *					match on 'term_id' and no code search.  Fastest response time. 
 *  
 * NOTES:   @search_text is used to search text for occurrences within coded objects as well as
 *				exact column name matches in user tables.     
 *  
 *			@does_not_contain removes results with provided text.  Does not apply to column name matches.  
 *  
 *			@obj_like restricts results to objects with names like text. i.e. 'upd' would only return results
 *				for objects with 'upd' somewhere in the name. 
 *
 *			@obj_type restricts results to objects with types like text. i.e. 'proc' would only return results
 *				for objects with xtypes P,RF, and X 
 *				(Stored procedure,Replication filter stored procedure,Extended stored procedure)
 *
 *			Deploy to any database where you want to search active code quickly.
 *  
 *  
 * Maintenance History:    
 * 03/09/2005 BONDJ - Created    
 * 11/13/2006 BONDJ - Updated for '05 
* -----------------------------------------------------------------------------------------------------------------------*/    
 
CREATE PROCEDURE [dbo].[find]
@search_text VARCHAR(255),
@does_not_contain VARCHAR(255) = NULL,
@obj_like VARCHAR(255) = '%',
@obj_type VARCHAR(50) = '%'        
 
AS        
 
DECLARE @results TABLE (
obj_name NVARCHAR(255),
obj_type NVARCHAR(255))      
 
IF @obj_type LIKE '%Table' OR @obj_type = '%'
	INSERT INTO @results
	SELECT t.name AS obj_name, 'Table' AS obj_type
	FROM sys.columns c
	INNER JOIN sys.tables t
		ON c.object_id = t.object_id
	WHERE c.name = @search_text       
 
IF @obj_type NOT LIKE '%Table'
	BEGIN
		INSERT INTO @results
		SELECT DISTINCT OBJECT_NAME(c.id),
		   CASE xtype
			WHEN 'C' THEN 'CHECK constraint'
			WHEN 'D' THEN 'Default'
			WHEN 'F' THEN 'FOREIGN KEY constraint'
			WHEN 'L' THEN 'Log'
			WHEN 'FN' THEN 'Scalar function'
			WHEN 'IF' THEN 'Inlined table-function'
			WHEN 'P' THEN 'Stored procedure'
			WHEN 'PK' THEN 'PRIMARY KEY constraint'
			WHEN 'RF' THEN 'Replication filter stored procedure'
			WHEN 'S' THEN 'System table'
			WHEN 'TF' THEN 'Table function'
			WHEN 'TR' THEN 'Trigger'
			WHEN 'U' THEN 'User table'
			WHEN 'UQ' THEN 'UNIQUE constraint'
			WHEN 'V' THEN 'View'
			WHEN 'X' THEN 'Extended stored procedure'
		   END
		FROM sys.syscomments c 
		INNER JOIN sys.sysobjects o 
			ON c.id = o.id
		WHERE [text] like '%' + @search_text + '%'  
 
		IF @does_not_contain IS NOT NULL AND RTRIM(@does_not_contain) <> ''
		 BEGIN
			DELETE r
			FROM @results r
			INNER JOIN sys.syscomments s
				ON r.obj_name = OBJECT_NAME(s.id)
			WHERE s.text LIKE '%' + @does_not_contain + '%'
		 END
 
	END 
 
 
SELECT obj_name,obj_type 
FROM @results 
WHERE obj_name LIKE '%' +@obj_like+ '%'
AND obj_type LIKE '%' +@obj_type+ '%'    
ORDER BY obj_type,obj_name      
 
RETURN