One day I discovered that the system table sysdepends in my database was not valid. I know that dropping and re-creating a stored procedure will update sysdepends for that single procedure, but what if I had 100s of stored procedures? Of course, there’s a script for that and for all who are too lazy to want to write that script themselves – here it is:

DECLARE  @name            NVARCHAR(128),
         @definition    NVARCHAR(MAX)

DECLARE cur CURSOR FOR
    SELECT        o.name, m.definition
    FROM        sys.sql_modules m 
    INNER JOIN    sys.objects o ON m.object_id = o.object_id
    WHERE        o.type = 'P'

OPEN cur

    FETCH cur INTO @name, @definition

    WHILE @@Fetch_Status = 0
    BEGIN
        EXEC('DROP PROCEDURE ' + @name)
        EXEC(@definition)
        
        FETCH cur INTO @name, @definition
    END

CLOSE cur

DEALLOCATE cur