Using Apex, how can we can we query all the fields and CreatedDate for Account or any other Standard Object?

Question

I need to run the below query for 200 fields changing the field each time and then copy the resulting most recent date.Is there any way to do this quicker than doing it 1 by 1?

SELECT name, CreatedDate FROM Account WHERE name!= null ORDER BY createdDate DESC NULLS FIRST

I tried to do this using an apex class:

public  class Allfields {

public void getallFieldValues(){
    Account account = new Account();
    // get all account fields from database schema
    Schema.SObjectType objectType = account.getSObjectType();
    set<String> accountFields = objectType.getDescribe().fields.getMap().keySet();
    system.debug(accountfields);
    string soql = '';
    for(String fld1:accountFields){
        soql = 'select '+fld1+',createddate from account ORDER BY CreatedDate DESC NULLS FIRST';
        getvalues(soql);
    }    
}
public void getvalues(String query){
    list<Account> newList=new list<Account>();
    newList = database.query(query);
    System.debug(newList);

}}

There are 500 different fields in the Account object and I am looking for a way to find the last time each of those fields were populated in a record so that I can go ahead and delete the fields that are never used.

But I am getting an error "duplicate field selected: CreatedDate". What am I doing wrong in my code here and is this how it's usually done? Thank you you for your time

Answer 1

The code in the OP will make a SOQL query for every single field on the object, which is going to hit limits very quickly and probably isn't what you want anyway. I'm guessing you actually want the code below. I've removed the CreatedDate from the list of retrieved fields since it will be in the list anyway, which is what is causing your error. Keep in mind that retrieving all the fields on an object can inflate its size in memory and cause you to exceed the heap limit, so be careful using unbounded code like this.

public void getallFieldValues(){
    // get all account fields from database schema
    Schema.SObjectType objectType = Account.getSObjectType(); //Note that you can actually call getSObjectType on the type name without instantiating a variable
    List<String> accountFields = new List<string>(objectType.getDescribe().fields.getMap().keySet());
    string soql = 'select '+ String.join(accountFields, ',') +' from account ORDER BY CreatedDate DESC NULLS FIRST';
    List<Account> result = Database.query(soql);
}

Answer 2

The reason you are appending "createddate" either you write an if block to exclude created date or remove your hardcoding. Below will work for you even if new field added or old field deleted.

soql = 'select '+fld1+' from account ORDER BY CreatedDate DESC NULLS FIRST';