Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to Recompile All SQL Server Database Objects


By:   |   Read Comments (19)   |   Related Tips: More > Maintenance

Attend a SQL Server Conference for FREE >> click to learn more


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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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 09, 2014 - 3:54:45 PM - Lewis Rearick Back To Top

How do recompile a DDL trigger?


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

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

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 02, 2013 - 3:16:36 PM - Jeremy Kadlec Back To Top

Hank,

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

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, January 02, 2013 - 12:03:29 AM - Hank Freeman Back To Top

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
hfreeman@msn.com

---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

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

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 05, 2012 - 2:48:45 PM - Jeremy Kadlec Back To Top

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

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

The scripts worked great.  Thank you.


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

thank you so much .

that was great


Friday, June 08, 2012 - 1:13:56 PM - Curious Back To Top

Can't you just use DBCC FREEPROCCACHE?


Monday, May 07, 2012 - 11:36:48 AM - Jeremy Kadlec Back To Top

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

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 06, 2012 - 12:46:09 PM - Jeremy Kadlec Back To Top

Don,

That is great to hear!

Thank you,
Jeremy Kadlec


Monday, February 06, 2012 - 12:39:51 PM - Don Back To Top

Jeremy,

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

Thank you.


Monday, February 06, 2012 - 12:05:07 PM - Jeremy Kadlec Back To Top

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 01, 2012 - 12:35:56 AM - Don Back To Top

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

 


Learn more about SQL Server tools