CODEWITHSUNDEEP
c#sql-serversql-server-2008
Patrick
Patrick

Reputation: 7712

SQL Server 2008 - Finding Specific Commands/Calls/Text

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.

Upvotes: 2

Views: 354

Answers (7)

Roland Bouman
Roland Bouman

Reputation: 31961

 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.

Upvotes: 1

user121301
user121301

Reputation:

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]".

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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.

Upvotes: 1

Rad
Rad

Reputation: 8381

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

Upvotes: 1

SirDemon
SirDemon

Reputation: 1758

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

Upvotes: 5

AdaTheDev
AdaTheDev

Reputation: 147224

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.

Upvotes: 1

rosscj2533
rosscj2533

Reputation: 9323

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

Upvotes: 1

Related Questions