45

I have created sObject and I need all the fields and I have many. Is there a way I can directly get all of them rather then typing each one of them.

2
  • 4
    you got lots of great suggestions here. Note that some fields have restrictions from being selected in the dynamic SOQL when more than one row can be returned - notably fields of type base64 such as Attachment.body
    – cropredy
    Sep 17 '14 at 23:22
  • As of the Spring '21 release this is possible using FIELDS( ALL )! You might want to update the accepted answer
    – Steven.B
    Feb 11 '21 at 17:51
40

Update: Spring '21 Release (API v51+) adds new SOQL FIELDS function can select all fields of an object. Refer to documentation

Knowing that new FIELDS function must have a LIMIT of at most 200, when used with ALL or CUSTOM keyword, the workaround below can still be useful.


There is no way to Select * with SOQL.

You can however work-around this limitation by using dynamic SOQL and describe methods to get all of the fields on the object. This is described in detail in this post by Cory Cowgill:

Building Dynamic SOQL - Select All Query

1
  • 6
    You can't use FIELDS(ALL) or FIELDS(CUSTOM) in Apex even with a LIMIT clause. These new features are essentially useless in Apex, as far as I can see. Feb 17 '21 at 0:04
19

I created some helper classes for this. You can find them here. There are 3 classes: xt, xs and xr. They contain also some other tools I couldn't separate for this answer, but the method you need is xs.query() in xs.cls. To get all straight into your Org, just use the "Deploy to Salesforce" link.

The usage is very simple. Just go somewhere to Execute Anonymous (e.g. in Eclipse), put ALL log-levels to WARN (to make the output readable) and try:

xt.log(xs.query('SELECT * FROM Opportunity LIMIT 2'));

or:

xt.log(xs.query('SELECT *, CreatedBy.* FROM Opportunity LIMIT 2'));

or even:

xt.log(xs.query('SELECT *, CreatedBy.*, Account.Owner.* FROM Opportunity LIMIT 2'));

to exclude some fields use:

xt.log(xs.query('SELECT *.not(Name|Probability) FROM Opportunity LIMIT 2')); 

The results should come close to your expectations. If you just want to compose the soql-string, you can use xs.soql(). xt.log() is just for demonstration purpose and very similar to system.debug().

Hints:

  • Supports relations for standard (Account.*) and custom fields (YourFile__r.*).
  • Supports distant relations like Whatever__r.WhateverElse__r.AndMore__r.*.
  • Queries work in non-prefixed and prefixed Orgs (Managed Packages).
  • The included tests are very poor and only to get some coverage.
  • There is a platform-limitation of the maximum length of the soql-string (about 20 000 characters). So if you use SELECT * ... on objects with too many custom fields, you will run into this limit.
  • To pass security reviews, you may have to change without sharing to with sharing in xs.cls.
  • Take care on FLS if you go without sharing.
  • If you use subselects, you need to do something like xt.log(database.query(xs.soql('SELECT* from Whatever') + ' WHERE WhateverField IN (yourSubQuery) '));
1
  • Einfach genial. Great job Uwe! Sep 17 '14 at 21:18
19

I suggest that rather than relying on some utility class, you become familiar with the platform's APIs so you can compose the appropriate solution each time you want to query all the fields, particularly if your queries are simple and only involve one object. Whoever gets to maintain the code in the future will thank you for keeping things simple and direct too.

Its as simple as this:

List<String> fields = new List<String>(Contact.SObjectType.getDescribe().fields.getMap().keySet());
String soql = ''
        + ' select ' + String.join(fields, ',')
        + ' from Contact'
        + ' where ...';
for (Contact c : (Contact[]) Database.query(soql)) {
    // ...
}

and the only part I would consider moving into a helper method would be the creation of the fields list.

PS

SOQL now includes the FIELDS keyword so select * is now just:

select fields(all) ...

but with the huge gotcha that FIELDS(ALL) and FIELDS(CUSTOM) are not allowed in Apex.

5
  • 1
    I found it helpful to cache the results of each field-describe since this again is limited to 100 and you may need the field-list elsewhere too. Queries often start simple and grow complex. The need of related fields like Account.WhatEver__r.* and the ability to exclude fields happened to me quite often. But eventually the reason for using a helper-class was the uneasy verbose syntax of the describe calls. During maintenance or extension of work, it burned us time simply to read our own code and took us back to the manual again and again... So a reusable SELECT * was really some kind of relief
    – Uwe Heim
    Sep 18 '14 at 6:17
  • 1
    @UweHeim I agree with your points. This seemed likely to be a simple case where the questioner would benefit from some insight into what the platform has to offer before they moved on to any framework or utility classes.
    – Keith C
    Sep 18 '14 at 7:27
  • 1
    @UweHeim Describe limits were removed since Summer 14 release Sep 18 '14 at 9:23
  • @mast0r that's very cool! Strangely system.limits.getLimitFieldsDescribes() still reports that limit to be 100 but you can simply go above and no governor throws an exception ;-)
    – Uwe Heim
    Sep 18 '14 at 11:44
  • @KeithC yeah, you're right, it certainly helps a lot to understand the backgrounds.
    – Uwe Heim
    Sep 18 '14 at 11:52
4

With Spring '21 Release (API v51+), the new SOQL FIELDS function can select all fields of an object.

Note that FIELDS function must have a LIMIT of at most 200, when used with ALL or CUSTOM keyword.

FIELDS(ALL) — to select all the fields of an object.

FIELDS(CUSTOM) — to select all the custom fields of an object.

FIELDS(STANDARD) — to select all the standard fields of an object.

Examples:

SELECT FIELDS(ALL) FROM Account LIMIT 200
SELECT FIELDS(CUSTOM) FROM Account LIMIT 200
SELECT FIELDS(STANDARD) FROM Account
SELECT Name, Id, FIELDS(CUSTOM) FROM Account LIMIT 200
SELECT someCustomField__c, FIELDS(STANDARD) FROM Account

More details from documentation

2

I have also written a utility class to achieve this behaviour. No tests, although naturally you would get coverage indirectly when testing other classes that use it.

https://github.com/lukemcfarlane/apexqueryutilities

2

An alternative is create custom query selecting all fields from workbench:

  1. Enter this URL https://workbench.developerforce.com
  2. Login with your credentials
  3. Go to queries tab
  4. Select SOQL Query
  5. Select your object and all its fields
  6. Once the query is generated, copy and paste it. If you need it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.