sp_Find

November 9, 2019 James Bond

Consulting, Scripts, Shortcuts, SQL Server, Tricks and Tips

sp_Find is at the top of my list of little simple secrets.

sp_Find is a stored proc that will help you in your quest to fully understand the lay of the land when working in a with a new client or any other new database. Although it can’t sit down with you and walk you through the design, it does serve up quick results when you identify a name, keyword or attribute you want to see if shows up elsewhere in DDL (table names, columns names, store procs, views, triggers, etc.). (Keep an eye out for sp_FindInData for when… you know, you need to search into actual table data.)

But SQLAgent007… I already have an add-on in SSMS for this.

Sure, you may…. but I defy you to be able to maneuver around search results faster then using sp_find in conjunction with complimentary query shortcuts. (Be sure to get on the list for our upcoming Tricks w/ Hotkeys course to learn how to!)

[thrive_lead_lock id=’660′]

USE [master]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.sp_Find') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
     DROP PROC dbo.sp_Find     
GO
/*=========================================================================================================
	Procedure:  sp_Find 
 ==========================================================================================================
DESCRIPTION: sp_Find is the fastest way to find DDL references in your SQL Server code.  This one works
great as a query shortcut.

For more insight visit:  https://jamesbond.consulting/sp_Find

PROVIDED BY:
James Bond Consulting (JBC)
Written by: James Bond
(c) 2019, https://jamesbond.consulting. All rights reserved.
email: [email protected]

You may alter this script (the "Software") for your own in-house purposes. You may also republish or blog 
code including without limitation the rights to use, copy, modify, merge, distribute, sublicense, and/or 
sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject 
to the following conditions:

§ The above copyright notice and this permission notice shall be included in all copies or substantial 
portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

=============================================================================================================*/ 
CREATE PROC dbo.sp_Find
@search varchar(100)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SQLString NVARCHAR(MAX),
		@ParmDefinition NVARCHAR(MAX),
		@db SYSNAME = DB_NAME(),
		@name VARCHAR(255),
		@fullname VARCHAR(255),
		@id BIGINT,
		@colid BIGINT,
		@objectType NVARCHAR(255),
		@buf VARCHAR(MAX),
		@pos1 INT,
		@pos2 INT,
		@line VARCHAR(MAX),
		@lineno INT,
		@c INT,
		@max INT

CREATE TABLE #objects (
ID INT IDENTITY(1,1) PRIMARY KEY,
[Name] VARCHAR(255), 
syscomments_id INT, 
colid INT,
ObjectType NVARCHAR(255)
) 

CREATE TABLE #out (
ID INT IDENTITY(1,1) PRIMARY KEY,
ObjectName VARCHAR(255), 
LineNumber INT, 
FoundInText VARCHAR(4000),
ObjectType NVARCHAR(255)
) 
 
--PREP CODED OBJECT REVIEW
SET @SQLString =  
     N'INSERT INTO #objects ([Name],  syscomments_id, colid,ObjectType)
		SELECT o.[name], c.id, colid, v.name
		FROM 
			'+@db+'.sys.syscomments c WITH (NOLOCK)
		JOIN 
			'+@db+'.sys.sysobjects o WITH (NOLOCK)
				ON c.id=o.id
		INNER JOIN
				master..spt_values v WITH (NOLOCK)
					ON o.type = RTRIM(LEFT(v.name,2))			
		WHERE
			category=0
			AND v.type = ''O9T''
		ORDER BY 
			o.[name], colid';  

EXECUTE sp_executesql @SQLString

--GET TABLE OBJECT HITS
SET @SQLString =  
     N'INSERT INTO #out(ObjectName,FoundInText,ObjectType) 
		SELECT OBJECT_SCHEMA_NAME(object_id)+''.''+name, ''FOUND IN TABLE NAME'',''U : user table''
		FROM 
			'+@db+'.sys.tables WITH (NOLOCK) 
		WHERE 
			[name] LIKE ''%'' + @Search + ''%''

		UNION

		SELECT OBJECT_SCHEMA_NAME(t.object_id)+''.''+t.name, ''COLUMN NAME: '' +c.name,''U : user table''
		FROM 
			'+@db+'.sys.columns c  WITH (NOLOCK)
		INNER JOIN 
			'+@db+'.sys.tables t WITH (NOLOCK)  
				ON c.object_id = t.object_id  
		WHERE 
			c.name LIKE ''%'' + @Search + ''%''';  

SET @ParmDefinition = N'@Search NVARCHAR(1000)';  

EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @Search = @Search; 

SET @buf=''
SET @lineno=1
SELECT @c = 1
SELECT @max = MAX(ID) FROM #objects


WHILE @c <= @max
BEGIN
	SELECT  @name = [name], @id = syscomments_id, @colid = colid, @objectType = ObjectType  FROM #objects WHERE ID = @c;
	SELECT @buf=''

	SET @SQLString =  
     N'SELECT @bufo=@buf+cast([text] AS VARCHAR(MAX)) FROM sys.syscomments WHERE id=@id AND colid=@colid';  
	SET @ParmDefinition = N'@buf VARCHAR(MAX),@id INT, @colid INT, @bufo VARCHAR(MAX) OUTPUT ';  

	EXECUTE sp_executesql @SQLString, @ParmDefinition,  
						  @buf = @buf,
						  @id = @id,
						  @colid = @colid,
						  @bufo = @buf OUTPUT  

	SET @pos1=1
	SELECT @pos2=charindex(CHAR(10), @buf, @pos1)

	WHILE @pos2>0
	BEGIN
		SELECT @line=SUBSTRING(@buf, @pos1, @pos2-@pos1), @lineno=@lineno+1

		IF CHARINDEX(@Search, @line)>0
		BEGIN
			SET @fullname = OBJECT_SCHEMA_NAME(@id)+'.'+@name
			SET @line =REPLACE(@line, CHAR(9), '')
			INSERT INTO #out(ObjectName,LineNumber,FoundInText,ObjectType) VALUES (@fullname, @lineno, @line,@objectType )
		END

		SELECT @pos1=@pos2+1
		SELECT @pos2=CHARINDEX(CHAR(10), @buf, @pos1)
	END

	SET @buf=SUBSTRING(@buf, @pos1, 4000)

	SELECT @c = @c + 1

	IF @colid=1 
		SET @lineNo=1

END

SELECT * FROM #out

DROP TABLE #objects
DROP TABLE #out

RETURN

[/thrive_lead_lock]