Re: Generate a CRUD matrix

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 15 Jan 2010 06:38:29 -0800 (PST)
Message-ID: <f4e4ce06-d94d-41cd-98d2-444d9c22d88a_at_m26g2000yqb.googlegroups.com>

On Jan 14, 6:40 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 14, 12:36 pm, vsevolod afanassiev
>
> <vsevolod.afanass..._at_gmail.com> wrote:
> > I don't think it is possible: a stored procedure may select/insert/
> > update/delete many tables, something like that
>
> > SELECT
> > FROM TABLE_A
>
> > UPDATE
> > TABLE_B
>
> > DELETE TABLE_C
>
> > Searching USER_SOURCE won't be enough
>
>  What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)?  Once you
> get the candidate list, you parse the procedures for their tables.
> Select is easy, just whatever follows FROM until no more commas,
> update and delete will always be one or two words after?  Did I
> misread the OP or miss something obvious?  I was thinking of getting
> the various elements separately (these procedures update, containing
> tables x, y z..., etc), but maybe easier just to get procedure/table/
> elements parsing directly, put in table, then do what you want.
>
> jg
> --
> _at_home.com is bogus.http://thedailywtf.com/Articles/The-Little-Red-Switch.aspx

I do not think parsing the from clause would be all that easy since you have to allow for encountering terms like inner join, right outer join, etc... then what about dynamic SQL where portions of the SQL statement are going to be held in variables? You may also have to deal with commented out sections of source.

I would be interested in getting a copy of a routine that can automatically extract SQL from stored code even if it had no ability to deal with dynamic SQL (since no well written system should use more than a few dynamic SQL statements).

HTH -- Mark D Powell -- Received on Fri Jan 15 2010 - 08:38:29 CST