Technical Article

Recompile Compiled Objects

,

This stored procedure, updates the schema version of user table(s), which in turn marks the Stored Procedures and Triggers for recompile.

CREATE procedure dbo.P_ZDBA_Recompile @dbname varchar(30)
As
Set NoCount On
declare @strmsg nvarchar(2000), 
        @strdbname varchar(30)
            
Set     @strdbname = @dbname  

Select @strmsg = N'USE ' + @strdbname + char(13) + N'exec 
sp_MSforeachtable  "DBCC LockObjectSchema (''?'')",
@whereand="and exists (select name from sysobjects 
                       where o.type = ''U'')"'

exec sp_executesql @strmsg

Return(@@error) --end of P_ZDBA_Recompile

Rate

Share

Share

Rate