Querying Salesforce Object Column Names w/SOQL

Question

I am using the Salesforce SOQL snap in a SnapLogic integration between our Salesforce instance and an S3 bucket.

I am trying to use a SOQL query in the Salesforce SOQL snap field "SOQL query*" to return the column names of an object. For example, I want to run a SOQL query to return the column names of the "Account" object.

I am doing this because SOQL does not allow "Select *". I have seen code solutions in Apex for this, but I am looking for a way to do it using only a SOQL query.

Noellenoellyn

Answer 1

You want to query metadata? Names of available tables, names of columns you can see in each table, maybe types instead of real Account/Contact/... data, correct?

You might have to bump the version of the API up a bit, current is 47 / 48 so some objects might not be visible in your current one. Also - what API options you have? SOAP, REST? Is "Tooling API" an option? Because it has very nice official FieldDefinition table to pull this.

It's not perfect but this could get you started:

SELECT EntityDefinition.QualifiedApiName, QualifiedApiName, DataType
FROM FieldDefinition
WHERE EntityDefinition.QualifiedApiName IN ('Account', 'Contact', 'myNamespace__myCustomObject__c')

I don't see the table in the REST API reference but it seems to query OK in Workbench so there's hope.

Generally try to Google around about EntityDefinition, FieldDefinition, EntityParticle... For example this is a decent shot at learning which tables are visible to you:

SELECT KeyPrefix, QualifiedApiName, Label, IsQueryable, IsDeprecatedAndHidden, IsCustomSetting
FROM EntityDefinition
WHERE IsCustomizable = true AND IsCustomSetting = false

Or in a pinch you could try to see which fields your user has permission to query. It's bit roundabout way to do it but I have no idea which tables your connector can "see".

Jarrett

Yeah, I am dynamically feeding the table name into the query (the end result of which needs to basically be a SELECT *), and since you can't use SELECT * in SOQL, I need to dynamically feed it the column names for the select also. I will try your idea, thanks! – Noellenoellyn

That worked, thanks! I changed the API version to 47. Too answer your earlier question, REST API and BULK API are available, but not tool. I was able to feed the table name into the where clause of your query and get the field names. – Noellenoellyn

Glad I could help! What are you doing, backup solution? Typically SF connectors like that have "connection wizard" that includes step to learn what tables are available, what fields they have and then there's way to select all. Granted, later you need to come back and add columns that were added in meantime... Just remember that even this is not 100% SELECT *. Your connecting user needs Profile right to read these fields – Jarrett

We are using SnapLogic to pipe all the Salesforce data into AWS S3, then Glue crawling it into Redshift. SnapLogic has connectors for Salesforce, but they are SOQL dependent. – Noellenoellyn

Answer 2

Starting from API version 51.0 there's the FIELDS() function available: it lets you query all fields of a given object similar to "SELECT *"

Example:

SELECT FIELDS(ALL) FROM User LIMIT 200

Reference: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_fields.htm

Thrasonical