Subject Re: deadlock while dropping stored procedure
Author Adam
--- In, Thomas Besand
<thomas.besand@...> wrote:
> Hi ng,
> using FB2 RC2, but I don't think my problem is specific to that version.
> In my DB I have a table HPQMAIN with some fields.
> There's also a stored procedure sp_findDups in the DB, that queries
> several fields in HPQMAIN and returns some result.
> Now I want to drop both table and procedure, when my client program
> requests it.
> Tried to 'DROP TABLE HPQMAIN;' but received dependency error regarding
> sp_findDups.
> Tried to 'DROP PROCEDURE "sp_findDups";' but received dependency error
> regarding HPQMAIN.
> Is there something like 'DROP DEPENDENCY <somedep>'?

No, the dependency is implied. A dependency means something like 'if
you drop this, you are in a pickle because <the other object> is
relying on it.

There are only a couple of ways I can think of that a table could
depend on a stored procedure. It must be either a trigger for that
table or a computed field that uses that procedure.

The stored procedure will have a dependency on the table if it queries
that table.

So it is pretty straight forward. Before you can drop an object, you
have to clear the dependencies.

If you wish to drop the table but it is used by the procedure, then
alter the procedure to not query the table. After you do that, you can
drop the table.