Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re-creating stored procedure

0 views
Skip to first unread message

Jean-Marc van Leerdam

unread,
Oct 6, 1997, 3:00:00 AM10/6/97
to

Choy Wai Kiong <cho...@hotmail.com> wrote:

>I need to drop and create some indexes in my database regularly and I am
>not sure whether I need to re-create my stored procedures after doing
>that. Can anyone shed some light on this issue? Thanks.

Just recompiling should be enough (to re-optimize any SQL on the
modified tables). I believe you can use sp_recompile for this purpose.

HTH

Jean-Marc
+------------------------------------------------------------+
|Jean-Marc.van.Leerdam@| All opinions expressed are just ... |
|ingbank.com | opinions (and my personal ones!). |
+----------------------+-------------------------------------+
(AntiSpam: please remove xxremovexx from the reply-to address)


Choy Wai Kiong

unread,
Oct 6, 1997, 3:00:00 AM10/6/97
to

I need to drop and create some indexes in my database regularly and I am
not sure whether I need to re-create my stored procedures after doing
that. Can anyone shed some light on this issue? Thanks.

Wai Kiong
cho...@hotmail.com

J. Virgulak

unread,
Oct 10, 1997, 3:00:00 AM10/10/97
to

Jean-Marc van Leerdam wrote:
>
> Choy Wai Kiong <cho...@hotmail.com> wrote:
>
> >I need to drop and create some indexes in my database regularly and I am
> >not sure whether I need to re-create my stored procedures after doing
> >that. Can anyone shed some light on this issue? Thanks.
>
> Just recompiling should be enough (to re-optimize any SQL on the
> modified tables). I believe you can use sp_recompile for this purpose.
>
> HTH
>
> Jean-Marc
> +------------------------------------------------------------+
> |Jean-Marc.van.Leerdam@| All opinions expressed are just ... |
> |ingbank.com | opinions (and my personal ones!). |
> +----------------------+-------------------------------------+
> (AntiSpam: please remove xxremovexx from the reply-to address)

This is one area that has confused me for some time. When the indexes
are dropped I believe the "indexdel" column in sysobjects is updated for
the relevant table. Now how does the sproc get the new object id
of the index ? Since I did not know how to answer that question,
I _assumed_ :) that the sproc needed to be dropped and created to
re-resolve to the new object id of the newly created index.
Apparently I have a misunderstanding since the experiments that I have
run show sp_recompile to be sufficient.


--jim.

0 new messages