May 8, 2002 at 10:19 am
I have a inherited a new database that uses a stored procedure to recompile all of the tables in the database and can find no documentation about recompiling tables only stored procedures. I do want this to mark procs for recompile and was told that when you mark a table for recompile that every dependent object is recompiled as well. Is this true and if so where are the docs that talk about this?
Scott Dexter
Manager Database Operations
Gamestop, Inc.
DEX
😀
The more you help the business, the more they will help you...well sometimes anyway.
May 8, 2002 at 10:21 am
Please send me email at sdexter@billmatrix.com My profile information has changed
Thanks
I have a inherited a new database that uses a stored procedure to recompile all of the tables in the database and can find no documentation about recompiling tables only stored procedures. I do want this to mark procs for recompile and was told that when you mark a table for recompile that every dependent object is recompiled as well. Is this true and if so where are the docs that talk about this?
Scott Dexter
Manager Database Operations
Gamestop, Inc.
[/quote]
Scott Dexter
Manager Database Operations
Gamestop, Inc.
DEX
😀
The more you help the business, the more they will help you...well sometimes anyway.
May 8, 2002 at 11:01 am
Right in BOL, look for the sp_recompile TSQL page. Not sure why you would want to do this on any regular basis.
Andy
May 8, 2002 at 11:19 am
quote:
I have a inherited a new database that uses a stored procedure to recompile all of the tables in the database and can find no documentation about recompiling tables only stored procedures. I do want this to mark procs for recompile and was told that when you mark a table for recompile that every dependent object is recompiled as well. Is this true and if so where are the docs that talk about this?
From Sybase docs:
An sp_recompile should be issued any time a new index is added or an update statistics. Dropping an index will cause an automatic recompile of all objects that are dependent on the table.
I'm not completely sure that the same goes for SQL server ( 6.5 - for sure), but probably there is something to look at.
Also sp_recompile looks for objname only in the current database and recompiles triggers and stored procedures only in the current database. sp_recompile does not affect objects in other databases that depend on the table.
You cannot use sp_recompile on system tables.
Edited by - EPol29 on 05/08/2002 11:39:39 AM
May 8, 2002 at 3:00 pm
I agree with Andy in that I see no reason why you would want to do this as you lose the advantage of stored execution plans unless you are making column name changes or datatype changes. Epol is right if indexes change an sp_recompile should automatically issue. Also if you really are wanting to force a recompile on stored procedures you can always add the WITH RECOMPILE statment to the Procs themselves but this of course will further hurt performance in most cases.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply