Add/remove columns of a table – code maintenance / optimisation

What is the best way to maintain code of a big project?

Let’s say you have 1000 stored procedures, and you have to add a new column to a table (or remove)

There might be 1-2 or 30 stored procedures, that might be affected.

Just a single “search” for the tablename might not be good enough, let’s say you only need to know the places where the table has insert/update/delete.

searching for ‘insert tablename’ might be a good idea, but you might have a space between those 2 words or 2 spaces, or a TAB … maybe the tablename is written like ‘[tablename]’ The same for all 3 (insert/update/delete.)

I am basically looking for some kind of ‘restricted dependencies’

How is this being handled the best way?

1. Keep a database table with this kind of information, and change that table every time you make changes to stored procedures?

2. keep some specific code as comment next to each insert/update/delete, and in this way, you will be able to search for what you need? Example: ‘insert_tablename’, ‘update_tablename’, ‘delete_tablename’

3. anyone having a better idea?

Answer

Ideally, changes are backward compatible. Not just so that you can change a table without breaking all of the objects that reference it, but also so that you can deploy all of the database changes before you deploy all of the application code (in a distributed architecture, think a downloadable desktop app or an iPhone app, where folks connect to your database remotely, this is crucial).

For example, if you add a new column to a table, it should be NULLable or have a default value so that INSERT statements don’t need to be updated immediately to reference it. Stored procedures can be updated gradually to accept a new parameter to represent this column, and it should be nullable / optional so that the application(s) don’t need to be aware of this column immediately. Etc.

This also demands that your original insert statements included an explicit column list. If you just say:

INSERT dbo.table VALUES(@p1, @p2, ...);

Then that makes it much tougher to make your changes backward compatible.

As for removing a column, well, that’s a little tougher. Dependencies are not perfect in SQL Server, but you should be able to find a lot of information from these dynamic management objects:

• sys.dm_sql_referenced_entities
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies

You might also find these articles interesting:

• Keeping sysdepends up to date
• Make your database changes backward compatible when adding a new column
• Make your database changes backward compatible when dropping a column
• Make your database changes backward compatible when renaming an entity
• Make your database changes backward compatible when changing a relationship