Find stored procedures not referenced in source code.

Question

I am trying to clean up a legacy database by dropping all procedures that are not used by the application. Using grep, I have been able to determine that a single procedure does not occur in the source code. Is there a way to do this for all of the procedures at once?

UPDATE: While using -E "proc1|proc2" produces an output of all lines in all files which match either pattern, this is not very useful. The legacy database has 2000+ procedures.

I tried to use the -o option thinking that I could use its output as the pattern for an inverse search on the original pattern. However, I found that there is no output when you use the -o option with more than one pattern.

Any other ideas?

UPDATE: After further experimenting, I found that it is the combination of the -i and -o options which are preventing the output. Unfortunately, I need a case insensitive search in this context.

Answer 1

feed the list of stored procedures to egrep separated by "|"

or:

for stored_proc in $stored_procs
do
grep $stored_proc $source_file
done

Answer 2

I've had to do this in the past as well. Don't forget about any procs that may be called from other procs. If you are using SQL Server you can use this:

SELECT name, text FROM sysobjects A JOIN syscomments B ON A.id = B.id WHERE xtype = 'P' AND text LIKE '%< sproc name >%'

Answer 3

I get output under the circumstances described in your edit:

$ echo "aaaproc1bbb" | grep -Eo 'proc1|proc2'
proc1
$ echo $?
0
$ echo "aaabbb" | grep -Eo 'proc1|proc2'
$ echo $?
1

The exit code shows if there was no match.

You might also find these options to grep useful (-L may be specific to GNU grep):

-c, --count
              Suppress  normal output; instead print a count of matching lines
              for each input file.  With the -v,  --invert-match  option  (see
              below), count non-matching lines.  (-c is specified by POSIX.)

-L, --files-without-match
              Suppress normal output; instead print the  name  of  each  input
              file from which no output would normally have been printed.  The
              scanning will stop on the first match.

-l, --files-with-matches
              Suppress normal output; instead print the  name  of  each  input
              file  from  which  output would normally have been printed.  The
              scanning will stop on the first  match.   (-l  is  specified  by
              POSIX.)

-q, --quiet, --silent
              Quiet;   do   not  write  anything  to  standard  output.   Exit
              immediately with zero status if any match is found, even  if  an
              error  was  detected.   Also see the -s or --no-messages option.
              (-q is specified by POSIX.)

Sorry for quoting the man page at you, but sometimes it helps to screen things a bit.

Edit:

For a list of filenames that do not contain any of the procedures (case insensitive):

grep -EiL 'proc1|proc2' *

For a list of filenames that contain any of the procedures (case insensitive):

grep -Eil 'proc1|proc2' *

To list the files and show the match (case insensitive):

grep -Eio 'proc1|proc2' *

Answer 4

Start with your list of procedure names. For easy re-use later, sort them and make them lowercase, like so:

tr "[:upper:]" "[:lower:]" < list_of_procedures | sort > sorted_list_o_procs

... now you have a sorted list of the procedure names. Sounds like you're already using gnu grep, so you've got the -o option.

fgrep -o -i -f sorted_list_o_procs source1 source2 ... > list_of_used_procs

Note the use of fgrep: these aren't regexps, really, so why treat them as such. Hopefully you will also find that this magically corrects your output issues ;). Now you have an ugly list of the used procedures. Let's clean them up as we did the orginal list above.

tr "[:upper:]" "[:lower:]" < list_of_used_procs | sort -u > short_list

Now you have a short list of the used procedures. Let's find the ones in the original list that aren't in the short list.

fgrep -v -f short_list sorted_list_o_procs

... and there they are.