Database Views: Modify SQL definition in Catalog

1738
7
10-28-2022 08:33 AM
Status: Open
Labels (1)
Bud
by
Notable Contributor

Edited as suggested by @JohannesLindner 


Regarding database views in geodatabases:

It would be helpful if we could edit the SQL view definition in ArcGIS Pro/Catalog. We can create database views in ArcGIS Pro. Why not let us update the SQL in Pro too?

Edit: It should also be possible to modify the SQL of a broken view. Related: Display database view SQL definition in Catalog properties, even when view is broken


Edit: It turns out it's possible to replace a database view using the Create Database View geoprocessing tool. Just use the same name as the existing view. See my comment below for details.

7 Comments
SSWoodward

Thanks for the Idea @Bud,

I think the idea is well placed here in the Data Management Ideas Exchange.  I've altered the label to 'Filtering - Relating Data', instead of Geodatabase, as it better matched the label description. 

Link to Label Descriptions

JohannesLindner

The viewing part was implemented in 2.7 thanks to this idea: ArcGIS Pro should allow you to see the SQL used to... - Esri Community

3:42 in this video: Ideas in ArcGIS Pro 2.7 - YouTube

 

So this idea would be about editing that query.

Bud
by

This idea is especially applicable to FGDB views. We need to be able to modify existing FGDB views in Catalog, since it's not possible to alter them in an SQL client (FGDBs are inaccessible outside of ArcObjects, unlike EGDBs).

We don't want to delete and recreate views every time we need to make a minor change to the SQL definition.

Jennifer_Parmeley

I wish I could up vote this by about 20. 

ESRI, WHY isn't this an existing feature?

Bud
by

ArcGIS Pro 3.1.1

It looks like it's possible to replace a database view using the Create Database View geoprocessing tool. The key is to create a view using the same name as the existing view.

Bud_0-1681318636521.png

That will replace the existing view. (Although it's unclear if it modifies the existing view or just deletes it and creates a new one.)


With that said, I think this idea is still valid. It would be best if we could edit the SQL definition of the existing view from the view's properties in Catalog:

Bud_1-1681318849834.png

That would be more intuitive.

SSWoodward

@Bud , This workflow will work as long as you have the 'Allow geoprocessing tools to overwrite existing datasets' option clicked on in the geoprocessing page of the options menu.

Without this clicked, the workflow will fail with 'Table already exists'.  The GP tool is creating a brand new view and not editing the original in place. 

GillesLavandier

A much needed feature...