SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[p_sys_Refresh_SP]
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE @sql                    NVARCHAR(600);

DECLARE @sp_name sysname 
	      
      
    DECLARE	c_sp INSENSITIVE CURSOR 
    FOR
		SELECT	name
		FROM	sysobjects
		WHERE	xtype = 'P'
	
	OPEN c_sp
	
	FETCH NEXT FROM c_sp INTO @sp_name
	
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	
		SET @sql = 'EXEC sys.sp_refreshsqlmodule N''' + @sp_name + ''''
		
		print 'SQL to execute = ' + @sql 
		exec (@sql)
	
		FETCH NEXT FROM c_sp INTO @sp_name
	END
	
	CLOSE c_sp
	DEALLOCATE c_sp			
		
END


GO
