
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sys_RefreshSysDepends]
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE
            @referencing_id         INT,
            @ref_db_name            NVARCHAR(255),
            @object_name            NVARCHAR(771),
            @referenced_id          INT,
            @sql                    NVARCHAR(600);

      DECLARE c CURSOR LOCAL FORWARD_ONLY
            FOR
            SELECT
                  ed.referencing_id,
                  ed.referenced_database_name,
                  COALESCE(ed.referenced_database_name + '.', '')
                  + COALESCE(ed.referenced_schema_name + '.', '')
                  + ed.referenced_entity_name,
                  ed.referenced_id

            FROM
                  sys.sql_expression_dependencies ed
            LEFT OUTER JOIN
                  sys.sysdepends sd
            ON
                  ed.referencing_id = sd.id
                  AND ed.referenced_id = sd.depid
            WHERE
                  sd.id IS NULL
                  AND ed.referenced_server_name IS NULL;

      OPEN c;

      FETCH NEXT FROM c INTO @referencing_id, @ref_db_name, @object_name, @referenced_id;

      WHILE (@@FETCH_STATUS = 0)
      BEGIN
            IF @referenced_id IS NULL OR @ref_db_name IS NOT NULL
            BEGIN
                  IF @referenced_id IS NULL AND @ref_db_name IS NULL AND OBJECT_ID(@object_name) IS NULL
                        SELECT OBJECT_SCHEMA_NAME(@referencing_id) + '.' + OBJECT_NAME(@referencing_id)
                                    + ' references ' + @object_name + ' (' + RTRIM(COALESCE(@referenced_id, 0)) + '),
                                          which doesn''t exist.';

                  IF @ref_db_name IS NOT NULL AND OBJECT_ID(@object_name) IS NULL
                        SELECT OBJECT_SCHEMA_NAME(@referencing_id) + '.' + OBJECT_NAME(@referencing_id)
                                    + ' might have a cross db dependency problem with ' + @object_name + '.';
            END
            ELSE
            BEGIN
                  SET @sql = 'EXEC sys.sp_refreshsqlmodule N'''
                        + QUOTENAME(OBJECT_SCHEMA_NAME(@referencing_id)) + '.'
                        + QUOTENAME(OBJECT_NAME(@referencing_id)) + '''';           
					
				  SELECT 'SQL to execute = ' + @sql;
				  	
                  EXEC sp_executesql @sql;

                  -- you can also log the calls you make here
                  -- by storing @sql in a table along with
                  -- event time, user information etc.

                  SELECT 'Executed SQL = ' + @sql;
            END
            FETCH NEXT FROM c INTO @referencing_id, @ref_db_name, @object_name, @referenced_id;
      END
      DEALLOCATE c;
END

GO
