CODEWITHSUNDEEP
sql-serverstored-procedures
Andez
Andez

Reputation: 5848

Determine errors in stored procedures if table design changes

This is more a hypothetical question, but suppose if someone makes a table change within SQL Server that breaks a Stored Procedure - or a large number of Stored Procedures, is there a way to determine what is broken?

Suppose I have a stored procedure which returns some user data

SELECT user.Id,
       user.FirstName,
       user.LastName
FROM Users

Then a developer makes a change to the table and changes LastName column name to Surname. But he forgets to change the related stored procedures.

I can use SQL Server to see the dependencies of a stored procedure, but I want to know which stored procedures are just broken.

Or in my case, if I have stored procedures that reference 3rd Party tables and the 3rd Party totally revamps their tables.

Is there any way to check?

Upvotes: 3

Views: 102

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

For procedures, you can use this cursor:

Declare list_cursor Cursor 
For
Select code = 'sp_refreshsqlmodule '''+OBJECT_SCHEMA_NAME(object_id)+'.'+OBJECT_NAME(object_id)+'''' From sys.procedures
Declare @sql nvarchar(max)

Open list_cursor
FETCH NEXT FROM list_cursor INTO @sql

While @@FETCH_STATUS = 0
Begin
    Begin Try
        Exec sp_executesql @sql
    End Try
    Begin Catch
        print @sql
        print '   '+ERROR_MESSAGE()
    End Catch
    FETCH NEXT FROM list_cursor INTO @sql
END

CLOSE list_cursor
DEALLOCATE list_cursor

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

You can use schema binding on views, functions, and in versions 14 and up stored procedures as well. This would be a proactive way to prevent a developer from making a change that would break a view, function, or procedure.

Otherwise you'll have to use a script to check. I think this question has some information that could help you: Syntax check all stored procedures?

Upvotes: 1

Related Questions