Post reply

recompile objects

  • March 10, 2004 at 2:34 pm

    #107690

    Hi there,
     
    Does anyone have any code that will guarentee to recompile all SPs, views and UDF's? 
     
    Often we find that once we've edited a table's schema, any attached UDF's stop working, usually requiring a "meaningless edit" to get SQL 2k to recompile them.
     
    Or has anyone any idea what I'm doing wrong? 🙂
     
    Thanks,

  • March 10, 2004 at 3:26 pm

    #498185

    For sp's:

    sp_recompile

    Causes stored procedures and triggers to be recompiled the next time they are run.

    Syntax

    sp_recompile [ @objname = ] 'object'

    -Not familiar with recompiling views or udf's

  • March 10, 2004 at 3:28 pm

    #498186

    Thanks for that Osoba

    actually i am looking for a common way to recompile all objects at once.

  • March 10, 2004 at 3:32 pm

    #498187

    All objects?  DBCC FREEPROCCACHE



    --Jonathan

  • March 10, 2004 at 3:44 pm

    #498189

    Thank u very much Jonathan.

    Thats what i am looking for

     

  • March 10, 2004 at 4:00 pm

    #498191

    Hi Jonathan

    It does not work with views,does it?

  • March 10, 2004 at 4:31 pm

    #498194

    No, but you could try something like this:

    DECLARE @v-2 nvarchar(258)

    DECLARE ViewCur CURSOR FOR

    SELECT QUOTENAME(Table_Schema + '.' + Table_Name)

    FROM INFORMATION_SCHEMA.VIEWS

    OPEN ViewCur

    FETCH NEXT FROM ViewCur INTO @v-2

    WHILE @@FETCH_STATUS = 0

    BEGIN

     EXEC('EXEC sp_refreshview ' + @v-2)

     FETCH NEXT FROM ViewCur INTO @v-2

    END

    CLOSE ViewCur

    DEALLOCATE ViewCur



    --Jonathan

  • March 10, 2004 at 4:32 pm

    #498195

    Thank you very much Jonathan

  • March 10, 2004 at 9:08 pm

    #498206

    by the way

    Hmm... but does that clear the on-disk compiled versions of the stored procedures as well?

  • March 11, 2004 at 5:03 am

    #498235

    There are no "on-disk compiled versions of the stored procedures."



    --Jonathan

  • August 30, 2004 at 9:12 am

    #521075

    exec sp_recompile <tablename> marks dependant stored procedures and triggers for recompilation.

    exec sp_refreshview <viewname> refreshes the metadata for views

    Does anyone know how to recompile or refresh user defined functions?

  • November 2, 2006 at 8:42 am

    #669513

    also, since [sp_recompile] just marks a proc to be recompiled on next execution... is there any way to force recompilation immediately?  aside from performing a 'meaningless edit'/ALTER statement (which means you have to script out the body of the proc in order to do so)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply