SysDepends.sql

-- Refactored from http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

CREATE VIEW dbo.SysDependsInfo
AS
SELECT ResultType
     , CASE ResultType
       WHEN 'reference'  THEN referencing_qualified + ' references ' + object_name + ' (' + referenced_id + '), which doesn''t exist.'
       WHEN 'dependency' THEN referencing_qualified + ' might have a cross-db depenency problem with ' + object_name + '.'
       WHEN 'sql'        THEN 'EXEC sys.sp_refreshsqlmodule N''' + referencing_qualified + ''''
       END AS Result
FROM (
    SELECT ed.referencing_id
         , ed.referenced_database_name AS ref_db_name
         , RTRIM(COALESCE(ed.referenced_database_name + '.', '') 
           + COALESCE(ed.referenced_schema_name + '.', '')
           + ed.referenced_entity_name) AS object_name
         , RTRIM(COALESCE(ed.referenced_id, 0)) AS 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
) AS Q
OUTER APPLY (
    SELECT
        OBJECT_ID(object_name) AS object_id,
        QUOTENAME(OBJECT_SCHEMA_NAME(referencing_id)) + '.' 
        + OBJECT_NAME(referencing_id) AS referencing_qualified
    ) AS A
OUTER APPLY (
    SELECT CASE
           WHEN object_id IS NULL AND ref_db_name IS NULL     AND referenced_id IS NULL THEN 'reference'
           WHEN object_id IS NULL AND ref_db_name IS NOT NULL                           THEN 'dependency'
                                                                                        ELSE 'sql'
           END AS ResultType
    ) AS B
GO

CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(600);
    DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
        FOR
        SELECT result
        FROM dbo.SysDependsInfo
        WHERE ResultType = 'sql'
    OPEN c;

    FETCH NEXT FROM c INTO @sql;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN         
        EXEC sp_executesql @sql;
        FETCH NEXT FROM c INTO @sql;
    END
    CLOSE c;
    DEALLOCATE c;
END
GO

CREATE TRIGGER DDL_Catcher ON DATABASE
FOR 
      CREATE_TABLE,     ALTER_TABLE,      DROP_TABLE,
      CREATE_PROCEDURE, ALTER_PROCEDURE,  DROP_PROCEDURE,
      CREATE_FUNCTION,  ALTER_FUNCTION,   DROP_FUNCTION,
      CREATE_TRIGGER,   ALTER_TRIGGER,    DROP_TRIGGER,
      CREATE_VIEW,      ALTER_VIEW,       DROP_VIEW,
      ALTER_SCHEMA,     RENAME
      /* 
            RENAME is new in SQL Server 2008.  If you
            try this in SQL Server 2005, you will get:           
            Msg 1084, Level 15, State 1, Procedure DDL_Catcher, Line 8
            'RENAME' is an invalid event type.
      */
AS
BEGIN
      SET NOCOUNT ON;

      -- you can log this somewhere for later review, or you
      -- can just comment it out:
      SELECT N'Fired for ', EVENTDATA().value
      (
            '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
            'NVARCHAR(MAX)'
      );     
      EXEC dbo.RefreshSysDepends;
END
GO

Joe,

Very nice! And if you're cursor-averse, you can always do something like this:

CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
SET NOCOUNT ON;

declare @Sql nvarchar(max) = N''
select @Sql = @Sql + result + char(31) + char(10)
from dbo.SysDependsInfo
where ResultType = 'sql'
exec sp_executesql @Sql;

END
GO

Thanks for this,
Ed

Oh, and add DISTINCT to the view definition:

SELECT distinct ResultType