Is it possible to query the schema of a Salesforce table using SOQL?
The MySQL equivalent is;
SELECT * FROM INFORMATION_SCHEMA.TABLES
Is it possible to query the schema of a Salesforce table using SOQL?
The MySQL equivalent is;
SELECT * FROM INFORMATION_SCHEMA.TABLES
Absolutely there is. Use the following syntax:
// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';
// then use database.query() method.
// Note its return value is a list<sObject> though you can cast it appropriately
list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);
You can also reference instance variables, but not any variable of an instance
set<id> myIdSet;
string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);
// XXXXXXXX The following WONT work X_X
map<id,Account> accountMap = new map<id,Account>(accountList);
string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()'; // X_X WILL NOT WORK
list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());
And since you mentioned there being no equivalant to SELECT * FROM ...
in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.
//================= QUERY UTIL METHODS =================================
//################# Format Fields For Query ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)
public static string FormatFieldsForQuery(sObject M){
return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)
public static string FormatFieldsForQuery(string objectName){
return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)
public static string FormatFieldsForQuery(string objectName,string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
if(objectType==null)
return fields;
for(string f :objectType.getDescribe().fields.getMap().keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(string objectName,string prefix)
public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
for(string f : m.keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)
public static string AllFieldsQuery(string objectName){
return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery
public static string AllFieldsQuery(sObject s){
return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery
Test Code:
//################# Format Fields For Query ###########################################
@isTest private static void testFormatFieldsForQuery(){
// Test order (depending upon input:
//
// #1 (sObject M, string prefix)
// #2 (sObject M)
// #3 (string objectName)
// #4 (string objectName,string prefix)
// #5 (map<String,Schema.SObjectField> m,string prefix)
// #6 AllFieldsQuery(string objectName) <---- different method name ----
///////////////////////////////////////////////////////////////////////////
test.startTest();
//test Account Standard Fields
Account a=new Account();
assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);
//test Contact Standard Fields
Contact c=new contact();
assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);
// bad objectName for
// #3 (string objectName)
// #4 (string objectName,string prefix)
system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));
}//END testFormatFieldsForQuery2
public enum fffqTestObj {Account,Contact}
public static void assertFFFQ(string fieldString,fffqTestObj objName){
set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));
if(objName==fffqTestObj.Account){
system.assert(fields.contains('name'));
system.assert(fields.contains('accountnumber'));
system.assert(fields.contains('ownerid'));
system.assert(fields.contains('site'));
system.assert(fields.contains('accountsource'));
system.assert(fields.contains('annualrevenue'));
system.assert(fields.contains('createdbyid'));
system.assert(fields.contains('jigsaw'));
system.assert(fields.contains('description'));
system.assert(fields.contains('numberofemployees'));
system.assert(fields.contains('fax'));
system.assert(fields.contains('industry'));
system.assert(fields.contains('lastmodifiedbyid'));
system.assert(fields.contains('ownership'));
system.assert(fields.contains('parentid'));
system.assert(fields.contains('phone'));
system.assert(fields.contains('rating'));
system.assert(fields.contains('sic'));
system.assert(fields.contains('sicdesc'));
system.assert(fields.contains('tickersymbol'));
system.assert(fields.contains('type'));
system.assert(fields.contains('website'));
}
else if(objName==fffqTestObj.Contact){
system.assert(fields.contains('accountid'));
system.assert(fields.contains('assistantname'));
system.assert(fields.contains('assistantphone'));
system.assert(fields.contains('birthdate'));
system.assert(fields.contains('ownerid'));
system.assert(fields.contains('createdbyid'));
system.assert(fields.contains('jigsaw'));
system.assert(fields.contains('department'));
system.assert(fields.contains('description'));
system.assert(fields.contains('donotcall'));
system.assert(fields.contains('email'));
system.assert(fields.contains('hasoptedoutofemail'));
system.assert(fields.contains('fax'));
system.assert(fields.contains('hasoptedoutoffax'));
system.assert(fields.contains('homephone'));
system.assert(fields.contains('lastmodifiedbyid'));
system.assert(fields.contains('lastcurequestdate'));
system.assert(fields.contains('lastcuupdatedate'));
system.assert(fields.contains('leadsource'));
system.assert(fields.contains('mobilephone'));
system.assert(fields.contains('otherphone'));
system.assert(fields.contains('phone'));
system.assert(fields.contains('reportstoid'));
system.assert(fields.contains('title'));
}
}//END assertFFFQ
My strategy here would simply be to describe a picklist field that implements the Global Picklist
.
SObjectField picklistUsingGlobal = SObjectType.MyObject__c.fields.PicklistUsingGlobal__c.getSObjectField();
List<PicklistEntry> entries = picklistUsingGlobal.getDescribe().getPicklistValues();
Best Answer
Schema information isn't available via query, however there is an API for it. Depending on what type of development you're doing:
They all work a bit differently so if you have specific questions about using one of them it might be best in a followup question.