Schema changes cause deleted rows in GEOMETRY_COLUMNS table

Topics: User Forum
Coordinator
Jun 17, 2007 at 10:32 PM
I received the following issue report in my email:


Change column order in data table with geom column cause deleted row in metadata table GEOMETRY_COLUMNS Some other column changes cause same problem. Can you help please?


When you make some kind of schema changes, SQL Server Management Studio generates a script that involves creating a new table, copying the data, dropping the old table and renaming the new table to the original name. The issue is that dropping a table with geometry columns, in order to keep integrity (what an irony this time), automatically deregisters the column from GEOMETRY_COLUMNS table. In this particular case, after the fact, I just can advice you to manually restore the missing rows in GEOMETRY_COLUMNS table. In general, I recommend you to take the following precautions:

0.- Always backup your database before an important change.

1.- Rename GEOMETRY_COLUMNS table to something else:
sp_rename 'ST.GEOMETRY_COLUMNS', 'GEOMETRY_COLUMNS_BAK'
2.- Make all those schema changes you think can drop the original table (there is an option to preview the generated SQL). In doubt, assume all changes will drop the table at some point.

3.- Rename GEOMETRY_COLUMNS back:
sp_rename 'ST.GEOMETRY_COLUMNS_BAK', 'GEOMETRY_COLUMNS'

Consider this procedure a workaround while I'm thinking in a better way to deal with this issue. Suggestions are welcomed.

Best regards,
Ricardo Stuven.