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

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

TobiSellekaerts — Thu, 30 Apr 2020 05:36:14 GMT

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.

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

MarcoBoeringa — Thu, 30 Apr 2020 06:52:39 GMT

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.

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

ThomasColson — Mon, 11 May 2020 11:54:46 GMT

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

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

KoryKramer — Mon, 11 May 2020 12:27:59 GMT

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-back-in-and-see-the-sql-that-was-used 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

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

TobiSellekaerts — Fri, 10 Jul 2020 09:36:12 GMT

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.

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

MarcoBoeringa — Fri, 10 Jul 2020 09:48:49 GMT

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:

Re: ArcGIS Pro should allow you to see the SQL used to create a database view - Status changed to: Implemented

KoryKramer — Wed, 16 Dec 2020 21:18:31 GMT

Implemented in ArcGIS Pro 2.7.

See

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

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

AlfredBaldenweck — Thu, 17 Aug 2023 21:27:16 GMT

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

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

KoryKramer — Thu, 17 Aug 2023 21:54:21 GMT

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

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-000154284