I am trying to figure out how specific table is loaded in Microsoft SQL Server Management Studio. Is there a way to search through all the Stored Procedure queries? I can chose to modify each one and search through the query but there are too many to manually search in that fashion. Any advice in relation to searching for strings in SQL Server Management Studio would be much appreciated. thanks
Add a comment
|
3 Answers
SELECT OBJECT_NAME(M.object_id), M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE '%YourTable%'
4 Comments
tylercomp
This may be working... Do you know how to match the object_id to a specific stored procedure?
Lamak
@tylercomp - I added the name to my answer
tylercomp
Awesome, do you know how to match object type as well?
Lamak
@tylercomp - Added a JOIN that shows you only the stored procedures.
Have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??
From here, I found the following snippet to search through the stored procs:
exec sp_stored_procedures 'a%'
OR
exec sp_stored_procedures @sp_name = 'a%'
1 Comment
tylercomp
hmmm, looks like that only returns the names... I am wanting to search the queries for these procedures...