Script to Recompile All SQL Server Database Objects

By:   |   Comments (19)   |   Related: > Maintenance


Problem

In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes.  After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled.  I know that sp_updatestats exists to update statistics against all objects in the database.  Does a similar stored procedure in SQL Server exist for sp_recompile?

Solution

sp_recompile is a system stored procedure in SQL Server 2008 R2, 2008, 2005 and 2000 that will recompile an object the next time it runs.  Recompiling an object is advantageous when 'indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency.  By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries' (Source = sp_recompile).  In essence the query plan changes and the SQL Server optimizer may not be getting the best query plan based on the recent changes.

In a different scenario, the parameter used to build the query plan may be good for some queries and bad for others.  If this is the case, it is necessary to determine when the stored procedure should be recompiled or determine if the performance is bad enough to have separated stored procedures to support the two different query plans.

Nevertheless, in SQL Server, I am not aware of a system stored procedure that will recompile all of the objects in a similar manner as sp_updatestats.  As such reference the scripts below as a simple means to recompile all of the objects in the database.

SQL Server 2008 R2, 2008 and 2005 Code to Recompile All Objects

CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS 
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spEXECsp_RECOMPILE 
-- Project: SQL Server Database Maintenance
-- Business Process: SQL Server Database Maintenance
-- Purpose: Execute sp_recompile for all tables in a database
-- Detailed Description: Execute sp_recompile for all tables in a database
-- Database: Admin
-- Dependent Objects: None
-- Called By: TBD
-- Upstream Systems: None
-- Downstream Systems: None
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified  | Developer  | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.07.2007 | JKadlec | Original code
-- 002 | N\A | 05.07.2012 | JKadlec | Updated code for SQL Server 2008 R2
*/

SET NOCOUNT ON 

-- 1 - Declaration statements for all variables
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table
(UIDTableList int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))

-- 2 - Outer loop for populating the database names
INSERT INTO @TableListTable(OwnerName, TableName)
SELECT u.[Name], o.[Name]
FROM sys.objects o
INNER JOIN sys.schemas u
 ON o.schema_id  = u.schema_id
WHERE o.Type = 'U'
ORDER BY o.[Name]

-- 3 - Determine the highest UIDDatabaseList to loop through the records
SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable

-- 4 - While condition for looping through the database records
WHILE @TableListLoop > 0
 BEGIN

 -- 5 - Set the @DatabaseName parameter
 SELECT @TableName = TableName,
 @OwnerName = OwnerName
 FROM @TableListTable
 WHERE UIDTableList = @TableListLoop

 -- 6 - String together the final backup command
 SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)

 -- 7 - Execute the final string to complete the backups
 -- SELECT @CMD1
 EXEC (@CMD1)

 -- 8 - Descend through the database list
 SELECT @TableListLoop = @TableListLoop - 1
END

SET NOCOUNT OFF
GO

SQL Server 2000 Code to Recompile All Objects

CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS 
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spEXECsp_RECOMPILE 
-- Project: SQL Server Database Maintenance
-- Business Process: SQL Server Database Maintenance
-- Purpose: Execute sp_recompile for all tables in a database
-- Detailed Description: Execute sp_recompile for all tables in a database
-- Database: Admin
-- Dependent Objects: None
-- Called By: TBD
-- Upstream Systems: None
-- Downstream Systems: None
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified  | Developer  | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.07.2007 | JKadlec | Original code
*/ 

SET NOCOUNT ON 

-- 1 - Declaration statements for all variables
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table
(UIDTableList int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))

-- 2 - Outer loop for populating the database names
INSERT INTO @TableListTable(OwnerName, TableName)
SELECT u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
 ON o.uid = u.uid
WHERE o.Type = 'U'
ORDER BY o.[Name]

-- 3 - Determine the highest UIDDatabaseList to loop through the records
SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable

-- 4 - While condition for looping through the database records
WHILE @TableListLoop > 0
 BEGIN

 -- 5 - Set the @DatabaseName parameter
 SELECT @TableName = TableName,
 @OwnerName = OwnerName
 FROM @TableListTable
 WHERE UIDTableList = @TableListLoop

 -- 6 - String together the final backup command
 SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)

 -- 7 - Execute the final string to complete the backups
 -- SELECT @CMD1
 EXEC (@CMD1)

 -- 8 - Descend through the database list
 SELECT @TableListLoop = @TableListLoop - 1
END

SET NOCOUNT OFF
GO

Sample Output

Object 'dbo.sysdiagrams' was successfully marked for recompilation.
Object 'dbo.ShoppingCartContents' was successfully marked for recompilation.
Object 'dbo.ShoppingCart' was successfully marked for recompilation.
Object 'dbo.Customer' was successfully marked for recompilation.
Object 'dbo.CreditCard' was successfully marked for recompilation.
Object 'dbo.Address' was successfully marked for recompilation.

Word of caution

Make sure you have a legitimate reason to recompile all of the objects in your database, especially during core business hours.  Although the process is typically quick, when all of the objects are recompiled, the first execution will be a little slower than normal.  The remaining executions should perform at the normal execution time.  This should be similar behavior as when you restart the SQL Server services or Windows (reboot).

Next Steps
  • Depending on the scenario needed to recompile all objects, consider changing the WHERE clause in the script above to fine tune the number of objects that get recompiled.
  • Depending on the number of objects in your database and the number of objects that will get recompiled, run the script in a development or test database to validate the time needed and the output.
  • Check out these related tips:
  • Script Alternatives - Use DBCC FREEPROCCACHE as an alternative to recompile all objects.  If a script is needed, an alternative to the loop is the sp_msforeachtable system stored procedure.  Just keep in mind that sp_msforeachtable is an undocumented system stored procedure, so check out the web for examples.  Special thanks to Mike B of the MSSQLTips.com community for this feedback.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Sunday, July 19, 2015 - 6:42:36 PM - Jonathan Po Back To Top (38252)

If the object name includes a period (i.e., "Labels.en-us"), the stored procedure will fail with the following error:

 

Msg 15165, Level 16, State 1, Procedure sp_recompile, Line 19

Could not find object 'dbo.Labels.en-US' or you do not have permission.

 

Here's the updated version with step 6 changed:

 

 

CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS 

/*

----------------------------------------------------------------------------

-- Object Name: dbo.spEXECsp_RECOMPILE 

-- Project: SQL Server Database Maintenance

-- Business Process: SQL Server Database Maintenance

-- Purpose: Execute sp_recompile for all tables in a database

-- Detailed Description: Execute sp_recompile for all tables in a database

-- Database: Admin

-- Dependent Objects: None

-- Called By: TBD

-- Upstream Systems: None

-- Downstream Systems: None

-- 

--------------------------------------------------------------------------------------

-- Rev | CMR | Date Modified  | Developer  | Change Summary

--------------------------------------------------------------------------------------

-- 001 | N\A | 06.07.2007 | JKadlec | Original code

-- 002 | N\A | 05.07.2012 | JKadlec | Updated code for SQL Server 2008 R2

*/

 

SET NOCOUNT ON 

 

-- 1 - Declaration statements for all variables

DECLARE @TableName varchar(128)

DECLARE @OwnerName varchar(128)

DECLARE @CMD1 varchar(8000)

DECLARE @TableListLoop int

DECLARE @TableListTable table

(UIDTableList int IDENTITY (1,1),

OwnerName varchar(128),

TableName varchar(128))

 

-- 2 - Outer loop for populating the database names

INSERT INTO @TableListTable(OwnerName, TableName)

SELECT u.[Name], o.[Name]

FROM sys.objects o

INNER JOIN sys.schemas u

 ON o.schema_id  = u.schema_id

WHERE o.Type = 'U'

ORDER BY o.[Name]

 

-- 3 - Determine the highest UIDDatabaseList to loop through the records

SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable

 

-- 4 - While condition for looping through the database records

WHILE @TableListLoop > 0

 BEGIN

 

 -- 5 - Set the @DatabaseName parameter

 SELECT @TableName = TableName,

 @OwnerName = OwnerName

 FROM @TableListTable

 WHERE UIDTableList = @TableListLoop

 

 -- 6 - String together the final backup command

 SELECT @CMD1 = 'EXEC sp_recompile ''' + '[' + @OwnerName + ']' + '.' + '[' + @TableName + ']''' + char(13)

 

 

 -- 7 - Execute the final string to complete the backups

 --SELECT @CMD1

 EXEC (@CMD1)

 

 -- 8 - Descend through the database list

 SELECT @TableListLoop = @TableListLoop - 1

END

 

SET NOCOUNT OFF

GO

Friday, May 9, 2014 - 3:54:45 PM - Lewis Rearick Back To Top (30708)

How do recompile a DDL trigger?

Tuesday, March 12, 2013 - 9:16:41 PM - Jeremy Kadlec Back To Top (22766)

Dan,

I am happy to hear it!

Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, March 12, 2013 - 7:04:57 PM - Dan Back To Top (22765)

Thanks Jeremy.  I like your stored procedure.

I made one change to add a databaseName as a parameter, then I can loop throught the databases I want to and call the recompiles sp.   Updated sp below...

 

-------------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.spEXECsp_RECOMPILE 

(

@DatabaseName sysname

)

 

AS 

/*

----------------------------------------------------------------------------

-- Object Name: dbo.spEXECsp_RECOMPILE 

-- Project: SQL Server Database Maintenance

-- Business Process: SQL Server Database Maintenance

-- Purpose: Execute sp_recompile for all tables in a database

-- Detailed Description: Execute sp_recompile for all tables in a database

-- Database: Admin

-- Dependent Objects: None

-- Called By: TBD

-- Upstream Systems: None

-- Downstream Systems: None

-- 

--------------------------------------------------------------------------------------

-- Rev | CMR | Date Modified  | Developer  | Change Summary

--------------------------------------------------------------------------------------

-- 001 | N\A | 06.07.2007 | JKadlec | Original code

-- 002 | N\A | 05.07.2012 | JKadlec | Updated code for SQL Server 2008 R2

*/

 

SET NOCOUNT ON 

 

-- 1 - Declaration statements for all variables

DECLARE @TableName nvarchar(128)

DECLARE @OwnerName nvarchar(128)

DECLARE @CMD1 nvarchar(4000)

DECLARE @TableListLoop int

DECLARE @TableListTable table

(UIDTableList int IDENTITY (1,1),

OwnerName nvarchar(128),

TableName nvarchar(128))

 

 

-- 2 - Outer loop for populating the database names

Select @CMD1  = '

SELECT u.[Name], o.[Name]

FROM ' + @DatabaseName + '.sys.objects o

INNER JOIN ' + @DatabaseName + '.sys.schemas u

 ON o.schema_id  = u.schema_id

WHERE o.Type = ''U''

ORDER BY o.[Name]'

 

INSERT INTO @TableListTable(OwnerName, TableName)

Exec  sp_executesql @cmd1

 

-- 3 - Determine the highest UIDDatabaseList to loop through the records

SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable

 

-- 4 - While condition for looping through the database records

WHILE @TableListLoop > 0

 BEGIN

 

  -- 5 - Set the @DatabaseName parameter

 SELECT @TableName = TableName,

 @OwnerName = OwnerName 

 FROM @TableListTable

 WHERE UIDTableList = @TableListLoop

 

 -- 6 - String together the final backup command

 SELECT @CMD1 = 'EXEC ' + @DatabaseName + '.dbo.sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)

 

 -- 7 - Execute the final string to complete the backups

 -- SELECT @CMD1

 EXEC (@CMD1)

 

 -- 8 - Descend through the database list

 SELECT @TableListLoop = @TableListLoop - 1

END

 

SET NOCOUNT OFF

GO

-------------------------------------------------------------------------------------------------------------------------------------

 

 

Wednesday, January 2, 2013 - 3:16:36 PM - Jeremy Kadlec Back To Top (21236)

Hank,

Thanks so much for your kind words, sharing your experiences and your script.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, January 2, 2013 - 12:03:29 AM - Hank Freeman Back To Top (21224)

Jeremy !

Thanks for such a well written post and thanks to all who have contributed.

I am working a large project where I needed to rename all the legarcy table Primary Keys, Foreign Keys and Indexes. Your post saved me from having to figure this out or use the EXEC sp_MSforeachtable process which as I believe still has issues even at SQL Server 2008 R2.

Again thanks for an excellent post ... I have one I will send you below, which I use to refresh all the views after table surgery (ALTERS)..  As you will see I went the extra mile to validate that my REFRESH works on all the views.  So the extra code is sort of like an audit of the script's processing.

Anyway, thanks again for your most excellent post and if you or others find fault in the script below or have a different style on how to do the same, please send your modification my way..

Hank Freeman
Senior SQL Server DBA/Data Architect
[email protected]

---use [databasename]  --- Code to refresh all the views in a database.
go
/***
DECLARE @viewName AS VARCHAR(255)
,@db_name sysname
select db_name(),
  s.name as schema_name,  v.name as view_name, v.object_id, d.referenced_entity_name -- , d.referenced_id
 from sys.views v
 inner join sys.schemas s on v.schema_id = s.schema_id
 left join sys.sql_expression_dependencies d
  on v.object_id = d.referencing_id
 where d.is_schema_bound_reference = 0
***/
--
DECLARE @viewName AS VARCHAR(255)
,@db_name sysname
,@ViewCnt int = 0
,@TotalViews int
set @TotalViews =
(
select count(v.name)
 from sys.views v
 inner join sys.schemas s on v.schema_id = s.schema_id
 inner join sys.sql_expression_dependencies d
  on v.object_id = d.referencing_id
 where d.is_schema_bound_reference = 0
---select count(name) FROM sysobjects WHERE xtype = 'V'
)
-------------------
DECLARE listOfViews CURSOR FOR
select db_name(),v.name
 from sys.views v
 inner join sys.schemas s on v.schema_id = s.schema_id
 inner join sys.sql_expression_dependencies d
  on v.object_id = d.referencing_id
 where d.is_schema_bound_reference = 0
 order by 2;
--SELECT db_name(),[name] FROM sysobjects WHERE xtype = 'V' order by 2
-- the above works for the dbname
OPEN listOfViews
--
FETCH NEXT FROM listOfViews into @db_name,@viewName
    WHILE (@@FETCH_STATUS <> -1)
    --while @@Fetch_Status = 0
    BEGIN
        EXEC sp_refreshview @viewName
        Set @ViewCnt = @ViewCnt + 1
        print ('Updated: ' + @db_name + ': '
        + @viewName + '... ViewCount: '
        + cast(@ViewCnt as varchar(10))+ ' of TotalViews: '
        + cast(@TotalViews as varchar(10))+'...')
        FETCH NEXT FROM listOfViews INTO @db_name,@viewName 
    END
CLOSE listOfViews
DEALLOCATE listOfViews
----------------

 

Thursday, October 18, 2012 - 9:18:14 AM - Jeremy Kadlec Back To Top (19977)

Vivek,

Are you trying to rename or recompile objects?

Thank you,
Jeremy Kadlec

Thursday, October 18, 2012 - 7:24:00 AM - Vivek Gupta Back To Top (19972)

I have so many SP and funtion, here i want to add dbo as prefix in all table who are using in SP and function.

 

Please guide me.

 

Thanks,

Vivek

Friday, October 5, 2012 - 2:48:45 PM - Jeremy Kadlec Back To Top (19806)

Ted,

Great point.  I know in the past when working with customers I have changed up the cursor logic, but if you need to recompile all objects then the sp_msforeachtable command can work very cleanly as well.

Thank you,
Jeremy Kadlec

Saturday, September 29, 2012 - 1:34:52 PM - Ted White Back To Top (19745)

Useful, thank-you.

 

Consider using the following to shorten your script.

 

EXEC sp_MSforeachtable @command1='EXEC sp_recompile ''?'''

Wednesday, August 15, 2012 - 2:45:06 PM - Jon Back To Top (19052)

The scripts worked great.  Thank you.

Tuesday, July 17, 2012 - 12:24:29 AM - Arman Back To Top (18556)

thank you so much .

that was great

Friday, June 8, 2012 - 1:13:56 PM - Curious Back To Top (17887)

Can't you just use DBCC FREEPROCCACHE?

Monday, May 7, 2012 - 11:36:48 AM - Jeremy Kadlec Back To Top (17310)

Sudhir,

Thank you for the feedback.  I have updated the tip to incorporate your code.

Thank you,
Jeremy Kadlec

Monday, March 26, 2012 - 7:09:48 AM - Sudhir Back To Top (16610)

This script needs small change:

FROM sys.objects o
INNER JOIN sys.schemas u
ON o.schema_id  = u.schema_id
WHERE o.Type = 'U'
ORDER BY o.[Name]

in order to find objects based on schema names.

 

Monday, February 6, 2012 - 12:46:09 PM - Jeremy Kadlec Back To Top (15913)

Don,

That is great to hear!

Thank you,
Jeremy Kadlec

Monday, February 6, 2012 - 12:39:51 PM - Don Back To Top (15912)

Jeremy,

I performed reindexing on a database and needed to mark the objects for recompilation. The script worked perfectly. 

Thank you.

Monday, February 6, 2012 - 12:05:07 PM - Jeremy Kadlec Back To Top (15911)

Don,

1 - Yes the script would need to be run in each database.

2 - I removed the TOP 10 logic in the script.

Please let me know how things go.

I will add this tip to my things to do list to provide a SQL Server 2008 version.

Thank you,
Jeremy Kadlec

Wednesday, February 1, 2012 - 12:35:56 AM - Don Back To Top (15860)

Jeremy, just a quick question.

  1. Does the script need to be executed once for (and from) each database ? 
  2. If I need to mark all objects (stored procedures and triggers) in a database to recompile, I am assuming I just need to remove TOP 10 from the Select statement in step 2a ?
Thank you in advance

 














get free sql tips
agree to terms