Hi,
Today i was working with some column removal and there were lot of stored procedures that i had to verify if they are fine or not.
So, wrote the following script to check each and every stored procedure for any breakages.
Copy and Execute on your database and enjoy!!!
DECLARE @spName nvarchar(1000)
DECLARE product_cursor CURSOR FOR
SELECT CONVERT(nvarchar(20), sch.name) + ‘.’ + CONVERT(nvarchar(500), obj.Name)
FROM sys.sql_modules modu
INNER JOIN sys.objects obj
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
ON modu.object_id = obj.object_id
WHERE obj.type = ‘P’ — for stored prcedures…
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @spName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @spName
exec sp_refreshsqlmodule @spName
FETCH NEXT FROM product_cursor INTO @spName
END
CLOSE product_cursor
DEALLOCATE product_cursor
Rizwan Ahmed 