/* Not every action forces objects to be recompiled during the next execution. For example creating indexes is such an action, but stored procedure could benefit from the new index. This Transact-SQL script marks all objects of all / predefined object type for recompilation during next execution, means it removes the cached plan, so new query plans are created. Other option would be e.g. to use a recompile hint like EXEC WITH RECOMPILE. Works with SQL Server 2005 and higher versions in all editions. Requires ALTER permission on the objects. Links: sp_recompile: http://msdn.microsoft.com/en-us/library/ms181647.aspx Recompiling Stored Procedures: http://msdn.microsoft.com/en-us/library/ms190439.aspx */ -- Mark Object For Recompilation DECLARE @P tinyint, @TR tinyint, @FN tinyint, @TF tinyint, @U tinyint, @V tinyint; DECLARE @Cnt int, @Object sysname; -- ToDo: Define below which object types should be marked for recompilation. -- Set variable = 0, if the objects of the type shouldn't be marked. -- Set variable = 1 (<>0), when all objects of that type should be marked to recompile. SET @P = 1; -- Stored Procedures SET @TR = 1; -- Triggers SET @FN = 1; -- Functions SET @TF = 1; -- Table Valued Functions SET @U = 1; -- User table SET @V = 1; -- Views DECLARE ObjCur CURSOR LOCAL FORWARD_ONLY FOR SELECT QUOTENAME(SCH.name) + N'.' + QUOTENAME(OBJ.name) AS ObjectName FROM sys.objects AS OBJ INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id WHERE ( OBJECTPROPERTY(OBJ.object_id, 'IsTable') <> 0 OR OBJECTPROPERTY(OBJ.object_id, 'IsExecuted') <> 0 ) AND ( (OBJ.type = 'P ' AND @P <> 0) OR (OBJ.type = 'TR' AND @TR <> 0) OR (OBJ.type = 'FN' AND @FN <> 0) OR (OBJ.type = 'TF' AND @TF <> 0) OR (OBJ.type = 'U ' AND @U <> 0) OR (OBJ.type = 'V ' AND @V <> 0) ); SET @Cnt = 0; -- Counter for info print. OPEN ObjCur; FETCH NEXT FROM ObjCur INTO @Object; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sys.sp_recompile @objname = @Object; SET @Cnt = @Cnt + 1; FETCH NEXT FROM ObjCur INTO @Object; END; -- Close and deallocate cursor. CLOSE ObjCur; DEALLOCATE ObjCur; -- Info printout. PRINT CONVERT(varchar, @Cnt) + ' object(s) marked for recompile'; GO
