Select to view content in your preferred language

ArcGIS Pro should allow you to see the SQL used to create a database view

3842
8
04-29-2020 10:36 PM
Status: Implemented
Labels (1)
TobiSellekaerts
Regular Contributor

In ArcGIS Desktop, you can use python or geoprocessing tools to create a database view via an SQL Query.  After the view has been created, it's useful to be able to see the SQL query used to create it.  In ArcCatalog, you could view this SQL by right clicking on the view, Properties, Definition tab. In ArcGIS Pro, there is no way to do this - once you've created the database view, you cannot go back in and see the SQL used.

Please provide some way of seeing the SQL used to create a database view in ArcGIS Pro.

8 Comments
MarcoBoeringa

Not a direct solution to your problem, but I have found DBeaver database IDE a really nice interface to manage my PostgreSQL database including views. Added benefit is has syntax highlighting and automatic formatting of SQL, something missing in ArcCatalog.

ThomasColson

Kory Kramer‌ Miss Me?!?!?! I believe this is already offered...

KoryKramer

Hi Tom - yeah, in all seriousness, I was wondering where you were   Glad to see you back!

This idea came from https://community.esri.com/message/923944-once-a-database-view-is-created-in-arcgis-pro-how-can-i-go... where they did discuss that the view can be added as a query layer.  It sounds like it doesn't show exactly the same syntax, and ideally we wouldn't have to add a layer to a map just to see this (you don't have to with ArcCatalog )

Thank you for the heads up on statuses - it is always helpful. 

I hope you're well.

Cheers

TobiSellekaerts

I finally got around to checking this, and the table properties (from within a map in Pro) do not show me what yours do.  Apologies in advance for the anonymization here. 

I get:

Data type: Query Table

Server: (sql server machine name)

Instance: (database name)

Client: sqlserver

User: (logged in database user)

Query:select Field1, Field2, Field3, Field4, Field5 from database.user.view

This query is simply 'select all the fields from the view'

What I need to see is the SQL used to generate the view itself, so something like this:

select table1.field1, table1,field2, table2.field3, table2.field4 from table1, table2...

I'm using 2.5 at the moment.

MarcoBoeringa

You mean the "Definition" TAB of the Properties dialogs?

That is indeed missing in Pro. However, I still think having a database IDE like DBeaver for this, is a reasonable alternative though, and comes with better and more options like auto-formatting etc. The display in ArcCatalog is pretty much unusable for complex view definitions:

KoryKramer
Status changed to: Implemented

Implemented in ArcGIS Pro 2.7.

See Ideas in ArcGIS Pro 2.7: Something to Celebrate 

Also check out what's new in ArcGIS Pro 2.7!

AlfredBaldenweck

Hi, I created a view in a file gdb and I can't see the definition after the fact. Are there plans to add this functionality to file GDBs?

I can see the definition in mobile GDBs and Enterprise GDBs, but not in a file GDB. (Pro 2.9.5)

AlfredBaldenweck_0-1692306706741.png

 

KoryKramer

@AlfredBaldenweck Unfortunately there was a regression. See https://community.esri.com/t5/arcgis-pro-ideas/view-sql-definition-of-fgdb-database-views/idi-p/1238... 

I see you're still on 2.9.5. The bug on the support site indicates that it was patched in 2.9.8 so if you install the latest 2.9.x patch (10 at this point I believe), this should be resolved: https://support.esri.com/en-us/bug/a-view-definition-is-not-displayed-for-file-geodatabase-bug-00015...