Auto Re-Compile SP and Views Possible?

• johna1

  Ten Centuries

  Points: 1184

  More actions

  April 27, 2004 at 5:01 pm

  #152123

  When a new field is added to a table, all the Views and Stored Procedure that is using that table needs to be re-compiled even though they may not be using that new field.

  Is there any easy way to re-compile all the Stored Procedure and Views for a given database in SQL2K? 

• Angela But

  Hall of Fame

  Points: 3848

  More actions

  April 28, 2004 at 11:09 pm

  #504599

  John,

  sp_recompile 'table_name'

  will recompile all objects (eg, stored procedures) that use a given table to be recompiled the next time they are run.

  Also have a look at DBCC FREEPROCCACHE in BOL.

  BTW, don't forget to update your statistics when you add another column, this will also affect your query plans.

  Cheers,

  Angela