derek hubbard

A colleague of mine and I were confronted with an issue today when we did not perform a proper code search before making some database schema changes to an existing SQL Server 2008 database.  This made me realize that I don't think I've ever performed an impact analysis on code within SQL Server.  Impact analysis on my application code is like second nature to me, but the idea of analyzing my SQL Server code was not something I had thought of before.

Impact analysis is extremely important when making changes to an existing application - especially if the changes you are making include database schema changes.  This is even more important if the application you are changing is an existing production application. if users are working with your application, they aren't going to be happy if they have runtime errors in their application every time you touch the codebase.  The specific project my colleague and I are working on is still in development, so we didn't cause any major production issues as a result of the schema changes we made, however, this may not be the case every time we make schema changes in the future.

A quick search on StackOverflow shows that I'm not the first developer to ask this question.  Also, I'm sure I'm not the last person to ask about it either, so I thought it was worth repeating here in case anyone else is interested.

In my scenario, I needed a way to search all of the existing stored procedures to see if an existing database column and relationship were referenced.  The easiest way to do this is a quick keyword search through all of the stored procedures.  Our database has over 100 stored procedures, so searching each one by hand wasn't feasible.  Plus, we're developers - when we are confronted with situations like this in the future, we should be looking for a quick way to automate this type of task.

One of the responses to this question on StackOverflow suggest running a quick SQL query against the database you want to search, plugging in the keyword you want to search for.

SELECT DISTINCT(sysobjects.name)
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'P'
AND sysobjects.category=0
AND CHARINDEX(<the keyword>,syscomments.text)>0

Just replace the placeholder '<the keyword>' with the keyword you want to find.  This query can easily be thrown into a stored procedure (with the keyword as a parameter) and you can call this every time you need to perform an impact analysis on your SQL Server stored procedures before making changes in the future.  Your colleagues. and your boss. will appreciate this, trust me.