CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
dotnet-practitioner
dotnet-practitioner

Reputation: 14148

identify insert stored proc

How do I identify stored proc that inserts records into "TableXYZ"?

I could have hundreds of stored procs.

Please help.

Upvotes: 0

Views: 168

Answers (5)

Jason Kresowaty
Jason Kresowaty

Reputation: 16520

The add-in RedGate SQL Search, currently free, can be very helpful as it lets you search all of the code in your database. You could search for just the table name and see if that gets you close enough. If you consistently wrote your INSERT statement to look like INSERT INTO TableName, you could search for that entire phrase.

To do this kind of search manually as an T-SQL query, you can query the definition column in the sys.sql_modules system view. This provides you a lot of flexibility as you can use LIKE and/or PATINDEX to search for wildcards/patterns.

Upvotes: 0

ScottS
ScottS

Reputation: 8553

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%insert into dbo.tablexyz%' 
AND ROUTINE_TYPE='PROCEDURE'

You will need to adjust the like clause.

Upvotes: 0

Will A
Will A

Reputation: 25008

SELECT * FROM sys.sql_modules
WHERE definition like '%insert%TableXYZ%'

...gets you on the road to an answer.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332691

Use:

SELECT OBJECT_NAME(m.object_id), m.*
  FROM SYS.SQL_MODULES m
 WHERE m.definition like N'%INSERT INTO my_table_name%'

This will allow you to search for table reference(s).

Background:

SYSCOMMENTSand INFORMATION_SCHEMA.routines have NVARCHAR(4000) columns, while SYS.SQL_MODULES definition column is NVARCHAR(MAX). So if "my_table_name" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but ROUTINES truncates.

Upvotes: 4

atbebtg
atbebtg

Reputation: 4083

I believe that you should be able to view the dependencies of the table by right clicking it on the management studio. This should at the very lease show you all stored procedures working with that table.

Upvotes: 1

Related Questions