topic Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used? in ArcGIS Pro Questions

Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

TobiSellekaerts — Wed, 22 Apr 2020 10:01:19 GMT

I've successfully created a database view using ArcGIS Pro, using arcpy.CreateDatabaseView_management(). Now, I would like to see the SQL that was used in the creation of that view. I could do this in ArcCatalog by right clicking on the view, Properties, Definition tab. Is there a way to do this in ArcGIS Pro? I tried both right click, properties and arcpy.Describe(). Since I still have ArcCatalog I've resorted to using that to view the SQL used to create the view, but am hoping I've just missed something in ArcGIS Pro.

Using:

ArcGIS Pro 2.4

ArcCatalog 10.7.1

Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

JakeSkinner — Wed, 22 Apr 2020 11:13:33 GMT

Hi Tobi,

Add the view to a map > right-click on the view > Properties > Source tab > click the pencil next to Query:

You should then see the SQL:

Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

TobiSellekaerts — Wed, 22 Apr 2020 11:17:07 GMT

Fantastic - worked like a charm - many thanks!

Upon deeper inspection, this actually isn't returning the correct information.

In ArcCatalog, I see something like this:

CREATE VIEW ViewName AS SELECT OBJECTID, ATT2 AS NewAtt2, ATT3 AS NewAtt3, LEFT(SOMELONGATTRIBUTE, 40) AS NewShortAtt FROM Database.User.OriginalTable

which is what I'm looking for - the SQL which created the original view.

In ArcGIS Pro, I see something like this:

SELECT OBJECTID, NEWATT2, NEWATT3, NEWSHORTATT from Database.User.ViewName

which isn't what I'm looking for.

Apologies for not examining more closely before marking your reply as correct.

Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

BruceHarold — Wed, 22 Apr 2020 13:03:23 GMT

Jake that's a query layer, output from Create Database View isn't showing the query for me.

Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

TobiSellekaerts — Thu, 30 Apr 2020 05:40:19 GMT

Thanks Bruce and Jake for your responses. I've opened a case with Esri Support and heard back that "there does not appear to be a way to do this in ArcGIS Pro currently like there is in ArcMap." Support is lodging an enhancement request and at their request I've submitted a new idea to GeoNet:

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

I'm going to mark this question as answered, even though the answer is "You can't get there from here", and keep my fingers crossed for support in a future release.

Re: Once a Database View is created in ArcGIS Pro, how can I go back in and see the SQL that was used?

BruceHarold — Thu, 30 Apr 2020 14:22:31 GMT

Great, I believe this has caught the attention of the right people in development so thanks for bringing this up.