Automatically Re-Create Stored Procedures In SQL Server

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