Re: Q: Stored procedures and table dependency

william_riedy@fac.com wrote: > > In article <34BC7F8D.51C6@mcmail.com>, > watson@mcmail.com wrote: > > > > william_riedy@fac.com wrote: > > > > > > > > > How can I programatically (via SQL preferably) determine the tables > > > and/or views that a stored procedure references. From memory MS SQL and > > > Oracle both track dependencies in the database catalog in somewhat human > > > readable format. Informix does store the information but it appears to be > > > encoded or compiled. > > > > > > I know that I can reverse engineer all sp's via the dbschema command but > > > then I would have to write a wicked parser in C or another tool to > > > determine that stored procedure "S" needs table "T" and view "V". > > > > Personally I use a script but there again I like scripting 'cos I'm sad. > > It's straightforward, get each procedure and look for any SQL and > > retrieve the tables etc. > > > > Thanks Paul, *but* if you notice you state "look for any SQL". I want to > take human eyes out of the loop. I need to do it programatically. I > guess I will need to write my "wicked parser" to do what I want. > Sorry you misunderstood I meant computer 'look' ie get your script to do it. If you are on a site that has standard layout for code life it is very easy. If not then the parser need to be able to indentify table base statements. I dont'believe you need to go the hassle of using C just do it awk. > William Riedy > First Albany Corporation > == all opinions expressed are my own === > > -------------------==== Posted via Deja News ====----------------------- > http://www.dejanews.com/ Search, Read, Post to Usenet -- Paul Watson # WF Software Ltd # It said use Windows 95 or better Tel: +44 1436 674629 # in the installation guide Fax: +44 1436 678693 # so I put it on Unix www.wfsoftware.co.uk #