Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-01-30 : 19:14:52
|
I'm converting a bunch of badly named (sp_) stored procedures to proper names. However, I'm concerned that I won't find every instance of code that calls them.One handy thing I've found is that rather than deleting the old sp_ procedures, I'm replacing their guts with:DECLARE @vcObjname varchar(100)select @vcObjName=OBJECT_NAME(@@PROCID)insert into log_test (item,value) VALUES ('badproc',@vcObjName)...so at least I'll have a log of if they get called. After a week or so of no calls, I figure I can delete them safely.However, one of them is indeed being called. It's not from my app, so it must be from another stored procedure. I seem to recall some way of text searching stored procedures, but I can't seem to find it.Does anyone know how I can track down the calling procedure, or just do a text search across every procedure?Thanks-b |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-30 : 19:17:24
|
HiYou can search through the system tables, syscomments. But when using stored procs, you should have the scripts in you source control system somewhere  so you can use any file / text search tool to look for the text.Otherwise, you can script out all your stored procs using enterprise manager and search the scripts.Damian |
 |
|
|
jbkayne
Posting Yak Master
100 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-30 : 19:26:42
|
| yes, when you create a stored procedure, if the objects it depends on are not there at the time, the sysdepends entries are not made.Searching the text is the best option.Damian |
|
|
|
|
|
|
|
|
Find text in stored procedures?