SQL Server 2008 - Finding Specific Commands/Calls/Text

Question

I need to find an insert statement into a specific table. Unfortunately, the application isn't mine to begin with, there are over 100 stored procedures in the database, I've stepped through the code writing down every stored procedure name from begining to end of this process and I still cannot find it. I know that the process has to occur some where because another process that depends on it works properly. I just cant find it.

Is there some sort TSQL that would allow me to search across multiple stored procedures for a specific line of text... more specifically "INSERT INTO [tablename]"

UPDATE

I've tried using:

 SELECT routine_name, routine_type  
 FROM INFORMATION_SCHEMA.ROUTINES  
 WHERE ROUTINE_DEFINITION LIKE '%INSERT INTO [tablename]%' 

Straight out, replacing [tablename] with the name of my table. This returns 0 rows.

I DO have a procedure with that exact line in it, and I would have assumed that it would have been returned in the above query, but its not bringing back anything at all.

UPDATE #2

After using the redgate sql search tool (using INSERT INTO [tablename]) it returns 1 sp, but i'm still in denial that this is correct due to some of the tsql (at the end of the sp its dropping tables that are still in the database...) (this is where my in-experience at sql shines through)

I still cant find where this is being called from though...

UPDATE #3

I just did a search on the entire solution for 'INSERT INTO [tablename]' to see if they maybe DIDN'T use a stored procedure for this call, and nothing... didn't find it anywhere.

Answer 1

 SELECT routine_name, routine_type 
 FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_DEFINITION LIKE '%INSERT INTO [tablename]%'

warning: this may take a while. If your system is in heavy production use, don't run it on the entire table, but add some extra condition to search only a bunch of stored routines at a time

EDIT:

Perhaps you should try something else. Make a text dump of all routines, and use grep to search that. I am sure you can get a grep for windows. Dumping is easily done with management studio: navigate to the database, right click, and choose tasks > generate scripts.

Answer 2

Try

SELECT * FROM sys.syscomments
WHERE text LIKE '%CREATE PROCEDURE%'
AND text LIKE '%INSERT INTO%'

or

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%INSERT INTO%' 
AND ROUTINE_TYPE='PROCEDURE'

2nd one found here

Answer 3

Just to add to the previous answers which I won't repeat, I think it's a good idea to have all the database objects scripted (and kept in source control). So I'd add the suggestion that you kill 2 birds with one stone - script the database, then search the generated sql scripts.

Answer 4

Make a stored procedure out of it too!

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO 

/*
exec sp_find_procs_containing 'insert into tablename'
*/

CREATE PROCEDURE [dbo].[sp_find_procs_containing]    
@search VARCHAR(100) = ''
AS
SET @search = '%' + @search + '%'
SELECT         ROUTINE_NAME,    ROUTINE_DEFINITION
FROM        
INFORMATION_SCHEMA.ROUTINES
WHERE        
ROUTINE_DEFINITION LIKE @search
ORDER BY ROUTINE_NAME

GO

Answer 5

Have a look at this red-gate tool: sql search Still in beta but it ought to work

Answer 6

You probably have some LF, CR/LFs, or extra spaces in there that are preventing your search from working. Reduce your search string until you get a match (e.g., try just using the table name), or change it to: %INSERT%SOMETHING%INTO%SOMETHING%, etc.

Answer 7

While the application is running, try running Profiler to capture the queries executed. Events to watch are SQL:BatchCompleted, RPC:Completed, and SP:StmtCompleted.

Also note that the "INTO" word is optional for the INSERT statement so also search for just "INSERT [tablename]".