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

Hi All

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.

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

Thanks in advance.
Jim
LVL 67
Jim HornMicrosoft SQL Server Data DudeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I'm a beginner in this "cloud" of the "cloud" as well and looks like Salesforce.com is the most "secretive" of all that actually share the same "database" infrastructure and architecture on the backend << http://www.dbms2.com/2011/09/15/database-architecture-salesforce-com-force-com-and-database/ >>

and here's what I use to learn and dig in the background:

http://developer.force.com/cookbook/recipe/retrieve-a-list-of-objects-using-apex
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
Answered when I asked the question here:

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.

The first solution was not something that I could impliment.
Either way, lowering points / accepting for the effort.

Thanks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.