if you add a drop columns from a table, the column_id is dropped with it; then when you add additional columns, the next column_id is used.
so if you drop column 4 in a 7 column table, and add another, column_id4 is lost...columns 5 thru 7 do not shift over.
perfectly normal to see gaps in the column_id; that's by design.
if you have another process that is counting from 0 to max(column_id), you'd want to change it to use something like a For Each Column in ColumnCollection.
the only other alternative is to rebuild the tables; i'd recommend using the SSMS GUI, as it handles all the heavy lifting behind the scene...identifying all constraints/foreign keys/defaults/etc, dropping em building new table,dropping old table,renaming new, re-adding all the constraints back.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!