question

tess avatar image
tess asked

Script to find out broken stored procs and views in database

Hi All,

Several times we make changes in database where a column is removed/modified in one or more tables. This might break stored procs and views querying those tables. Is there a script which could pull out these bad stored procedures and views in a database ?

Thank you very much.

stored-proceduresscriptdependency-check
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

There's nothing built in that will do that for you. For views you can bind them to the schema, which will prevent you from making changes to the schema, but I don't think that's what you're looking for. You either have to check dependencies before you make a change (and that's not 100% accurate) or you need to run through the procedures and views after you make a change, doing a drop and recreate. Then the bad ones will get removed. That's the only way I know to do it.

On a side note, changing your development and deployment mechanisms so that you're storing your code in source control and then using something like Visual Studio Team System Database Edition or Red Gate SQL Compare or one some other mechanism to do full database builds and incremental changes with dependency checks will help to elminate this issue.

4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Or Team Foundation Server. VSS is going the way of the dodo, thank the gods.
1 Like 1 ·
DaniSQL avatar image DaniSQL commented ·
I just asked one of the developers working with me here and he told me they regressively test the application after they change the schema and notice if the sp is broken other than they dont have robust mechanism in place.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
There are ways to do unit testing in SQL, that requires some setup but is very often worth it. Also, if these procs/views are called by some application, the application probably has both unit tests and regression tests which can reveal it. As for Source Control, remember that SQL Server 2008 will partially integrate with Visual Source Safe, and that Red Gate is currently working on a program to integrate SSMS with several other source control mechanisms.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Grant - Amen. Oh and +1 :)
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

You can write a script to run sp_refreshsqlmodule on each procedure, view, trigger and function. You will receive errors for any objects that depend on other objects that are no longer in place...

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Did not know that one. Excellent! I love learning new stuff.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
It's one that Paul White pulled out on me about a year back - what a legend :)
0 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
Heh... great idea but I think the point is to find that out BEFORE you make the changes to the table so you can also make the necessary changes to the modules at the same time you make the changes to the table in production. Obviously, if you have a "perfect" replica of production somewhere, then you can do an AFTER the fact test like this... IF you have a replica of production. ;-) Still, +1 for the knowledge.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

There are third party tools that check for dependancies like this. Take a look at www.red-gate.com.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yonision avatar image
yonision answered
The following tool that we've developed does exactly that: http://nobhillsoft.com/Diana.aspx we're giving it away for free (no gimmicks) you can load your TSQL code and compile it. you'll get both errors and warnings , and among other things it'll tell you specifically whats broken and why
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.