Salesforce.com: How to find all tables and columns? (like SQL sys.tables, sys.columns)

Question

I've inherted an SSIS package where the data source is salesforce.com, using the Pragmatic Works controls TFSalesForce Source / Destination.

Ten tables, some of these tables have 700+ columns, and my client tells me that these change every once in awhile whenever users add/edit columns.

Question: What's the salesforce.com SOQL to 'find all tables and columns', similar to SQL Server sys.tables and sys.columns?

Answer 1

There's so such equivalent in salesforce. Fields and tables are exposed through metadata describe calls, not through the platform's query language. However, there are app exchange apps that can convert metadata into custom tables that you can query normally. Note that this is essentially cached data, so you'll have to periodically run a process that updates this cache.

Edit:

App Exchange Apps are apps hosted on the site http://appexchange.salesforce.com/, which can be installed by administrators into any organization that the package supports, possibly for an additional one-time or per-month fee.

The query you could use would be dependent on the package being used, but it would probably look something like this:

SELECT PKG__FieldType__c, PKG__FieldLength__c, PKG__DefaultValue__c, ... FROM PKG__Field__c

The exact semantics will vary by type, so you'll have to read the documentation or look at the data structures installed post-installation.

Updating the cache also varies by package. For example, one such app I tested had a Visualforce page with a button that a user could click to kick off the process. Other apps might use some scheduled process to update records every night or use some other process.

Answer 2

You may also want to look at Schema Lister developed by Tquila. Navigate to Schema Lister URL, provide your Salesforce credentials and it will list all your objects and fields. Here is a blog post describing Schema Lister http://www.tquila.com/blog/2014/11/19/list-all-your-objects-fields-and-field-attributes-introducing-schema-lister

Answer 3

Consider the Google Chrome Extension: SalesForce Inspector.

I've found it to be an excellent tool when mining SalesForce. While you're writing your SOQL query, it suggests & autocompletes fields in the SELECT clause and data objects (tables) in the FROM clause.

At the time of this writing, this doesn't work in OSX, but does in Windows.

Here's how you'd do it.

1. Open Chrome > Login to SalesForce > SalesForce Inspector (faded tab on the right side) > Data Export
2. Start a generic SOQL query: SELECT Id FROM
3. [CTRL] + [SPACE]
4. Small + on the right side of the data objects list to "expose all"

All available data objects are exposed to click-select (which will update your SOQL query).

Answer 4

You can generate an enterprise.wsdl file, then rename it with .xml. Import the XML file into Excel (Data menu) and you can see tables, columns, and types in a human-readable form.