How to update fields in a registered view

[JasonTipton]

I have tables that have multiple geometries (extent, centroid, boundary,... ) so they have multiple registered views on top of them. If I need to make changes to the fields, how should I do this?

For example, I need to change the size of a text field from 32 to 60. In SQL, I would just make the change and go about my way.... In arcpy, I would:

1. Rename the Field
2. Add the field back with the new length
3. Transfer the data
4. Remove the renamed field

I'm not able to do this on the views, so I tried on the base table. Arcpy ran through and succeeded, but when I look at the COLUMN_REGISTERY table, the COLUMN_SIZE is still 32. I guess I expected with all of ArcMap's overhead, that it would figure out that it needed to update these values, but I guess it doesn't store the link from view to table, so how could it....I'm really tempted to just update those values.

I'm kind of assuming here that anytime I make a change to the base table or need to add/remove a field, I will have to:

1. Unregister the View
2. Make the change on the table
3. [OPTIONAL] Make any changes on the view if necessary
4. Re-register the view

[VinceAngelo]

It is certainly best practice to drop (not unregister) and recreate the view when schema changes are made to the fields from which the view was constructed. 

If changes are made to a table outside of ArcGIS's ken, it might be enough to just re-describe the table with a connection as the owner (which is what an 'sdetable -o describe' would have achieved, back in the day).  Of course, such changes should never be made to versioned or archived feature classes, due to the conflicts that could result.

The same describe procedure might work with views, but it's very much dependent on how the RDBMS goes about validating and revalidating views, so the safe bet is to drop and recreate

- V

[JasonTipton]

Right, that's how to change it in the database...

But how do I get the ESRI metadata tables (specifically COLUMN_REGISTRY) to recognize the change? That's why I thought if I did it via arcpy, it would update the correct metadata tables. It did not, so I might as well have made the change directly with SQL.

This data is not versioned or archived. It is only registered with the Geodatabase. That's why I'm thinking un-register/ reregister would refresh the metadata.

I would really just as well like to update the column sizes directly via sql. I can't imagine it would hurt, and that's all arcpy would do anyway.

[VinceAngelo]

It isn't possible to "unregister" a view.  The only way to clear GDB metadata is to delete the object using Desktop (so some other geodatabase-aware tool, like a Python script). 

As you have seen, modifying the tables using SQL cannot update geodatabase metadata.

- V

[JasonTipton]

Which is exactly why I attempted to use Desktop to make this change.

So, If I "delete" the view, what exactly is it going to delete? Will it drop the view from the database and I will need to re-create the view and then re-register it? Surely, it won't drop the table as well?

Unfortunately none of this seems to be documented in the docs

[VinceAngelo]

If you use ArcGIS to delete a view, it will drop the view.  Only the view.

- V

[JasonTipton · Accepted solution]

If all you are needing to do is to increase the size of a field, don't worry about all this**.  Follow these steps.

1. Make the change in SQL

Done. ESRI manages the COLUMN_REGISTRY table by itself. It will eventually (the next time ESRI accesses it) figure out that you increased the length of the base table. Thanks ESRI!

If you are adding/removing columns from a registered view, according to Vince Angelo​:

1. Make a copy of your SQL view definition
2. Delete the registered view using ArcCatalog (or arcpy) -- Your view is now dropped
3. Make any changes to the base table in SQL
4. Re-create your view with the changes
5. Re-register your view with ESRI

** This applies to non-versioned data only. It does work for archive enabled content