Handy Script for Checking Breakages in SQL Server Objects (Views, Stored Procedures etc …)


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



PRINT @spName

exec sp_refreshsqlmodule @spName

FETCH NEXT FROM product_cursor INTO @spName


CLOSE product_cursor

DEALLOCATE product_cursor