February 27, 2009 at 2:03 pm
The syntax is sp_recompile @objname = 'objname'
The object name can be the name of a stored procedure. What if it is the name of a table? a view? ... What is happening while running this stored procedure against a table?
Any input will be greatly appreciated.
February 27, 2009 at 2:22 pm
From BOL:
If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 3:56 pm
Could you explain what recompile a table mean while using this stored procedure?
February 27, 2009 at 4:00 pm
It doesn't recompile the table but marks all the stored procedures that reference the table you used when executing sp_recompile, for recompilation.
I feel like a technical writer trying to explain that logically.
So, if you have procedure1, procedure2 and procedure3 that all reference tableA and you execute sp_recompile 'tableA', all of the procedures, 1, 2, and 3 will be marked for recompile.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 8:03 pm
Could you provide references or links for your statements?
February 28, 2009 at 5:59 am
It's in the Books Online, the SQL Server documentation. Just look up sp_recompile and read what's there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2009 at 6:11 am
SQL ORACLE (2/27/2009)
Could you provide references or links for your statements?
My first response was a direct quote from BOL (Books On Line). Look under the arguments section at the @objname parameter.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply