Search a codebase for references to table names

Question

I have a directory full of legacy code from a VB6 application.

I have been asked to provide a list of all the tables that this application uses, so we can assign a special SQL Server username to it.

What is the best way to scan a codebase for references to table names?

Some ideas I've had:

1. Search for the following keywords: "FROM", "UPDATE", "INSERT", and manually note the table name(s) surrounding those phrases.

   Problem: Lots of manual work

2. Run the application with a SQL Trace, and try to exercise each function, then scan the logs for table names

   Problem: Same manual work, plus I might overlook some obscure functions

Can anyone suggest better alternatives?

Answer 1

I would select from information_schema.tables and save the results to file to build a table list and then use a bat file or command line regex tool to use the table list as a source for comparisons against the files in the source code directory. You could output what files had a hit, and what table names were hit (what line the hit was on if you're interested). I'm not a grep whiz, but I think that would be the right sort of tool to use.

EDIT Depending on how the data access was handled, you might want to expand the search list to include stored procs from information_schema.routines

Edit 2 Approach using finstr, a cursor, and maybe the dark side

Please note that while the below should work, if pointed at the wrong directory, it could cause havoc. Also, it will only work if the source code is accessible from the server and xp_cmdshell is enabled. Maybe the whole idea is evil, I don't know.

create table #files (filepath   varchar(4000))
create table #tablesfound (tablename sysname, filepath varchar(4000))

declare @sql nvarchar(4000)
Declare @cmd nvarchar(400)
Declare @dir varchar(256)
Declare @tbl sysname
set @dir = 'source code directory with e.g. c:\source\'
declare crsX cursor for
Select table_name from information_schema.tables
open crsX
Fetch Next from crsX into @tbl

While (@@Fetch_Status = 0)
Begin
    set @cmd = 'findstr /S /M '  + quotename(@tbl, char(34)) + ' ' + @dir + '*.*'

    insert into #files exec xp_cmdshell  @cmd
    if exists (Select 1 from #files where filepath is not null)
    Begin
     insert into #tablesfound (tablename, filepath)
     Select @tbl, filepath from #files where filepath is not null
     delete from #files 
    End  
    print @cmd
    Fetch Next from crsX into @tbl
End
close crsX 
Deallocate crsX

Select * from #tablesfound