To Recompile All The Stored Procedures For Database

Recompiling an object is an advantage when the "indexes or other changes that affect statistics are made the database, compiled stored procedures and triggers may lose efficiency. Sp_recompile is a system stored procedure in SQL to rebuild an object, the next time it runs. Recompiling you can re-optimize queries.

Here is the query to recompile all the SPS in the database.

Declare @ name nvarchar (125), @ cmd nvarchar (300)

SPList declare the cursor / Cursor * It must be observed to go through all the SPS DB * /

Select the name of sys.objects

where type = 'P'

SPList open

FETCH NEXT from SPList / * get the record of the cursor * /

in the name @

While @ @ FETCH_STATUS = 0

start

Select @ cmd = 'EXEC sp_recompile [' + @ name + ']'

Exec @ cmd / * recompile the SP * /

FETCH NEXT SPList / * Get the name of SP after the cursor * /

@ In the name

Head

SPList close

Deallocate SPList