Using DBAmp System Views
Using DBAmp System Views
DBAmp provides system views that you can use to view object, field, relationship, and user entity access metadata of a Salesforce object. The DBAmp stored procedure, SF_CreateSysViews, generates the views data. The views are created by supplying the DBAmp linked server name:
EXEC SF_CreateSysViews '𝘚𝘈𝘓𝘌𝘚𝘍𝘖𝘙𝘊𝘌_𝘓𝘚'
where SALESFORCE_LS is the name of the DBAmp linked server. The command creates these views:
- SALESFORCE_Fields
- SALESFORCE_FieldsPerObject
- SALESFORCE_Objects
- SALESFORCE_Relationships
- SALESFORCE_UserEntityAccess.
The prefix of the view names is derived from the DBAmp linked server provided in the command.
You can access these views using any SQL SELECT statement.
The table below lists all available views and provides a description of each:
| View | Description |
|---|---|
| SALESFORCE_Fields SELECT * FROM SALESFORCE_Fields SELECT * FROM SALESFORCE_Fields WHERE ObjectName=’Account’ |
Contains information about Salesforce.com object fields. There is one row for each object field. Some of the columns in this view are: ObjectName—name of the object FieldName—name of the field IsCreateable—is the field insertable? Type—Field Type using sf terminology |
| SALESFORCE_Objects SELECT * FROM SALESFORCE_Objects SELECT ObjectName, KeyPrefix, IsEverCreatable FROM SALESFORCE_Objects WHERE ObjectName = ‘Contact’ |
Contains information about the Salesforce.com objects. There is one row for each object in your organization. Some of the columns in this view are: ObjectName—name of the object IsEverCreatable—is the object deletable? IsEverUpdatable—is the object updatable? KeyPrefix —prefix of the object’s SF ID |
| SALESFORCE_Relationships SELECT * FROM SALESFORCE_Relationships SELECT * FROM SALESFORCE_Relationships WHERE ParentObject = ‘Account’ and ChildObject = ‘Contact’ |
Contains information about the relationships between the Salesforce.com objects. There is one row for each relationship between objects in your organization. Some of the columns in this view are: ParentObject—name of the parent object ChildObject—name of the child object ParentToChildRelationshipName—the relationship name for the parent to child relationship (in SOQL) |
| SALESFORCE_UserEntityAccess SELECT * FROM SALESFORCE_UserEntityAccess WHERE UserID = ‘00530000000kQi6AAE’ NOTE: WHERE clause on UserID is required. UserID is the ID of a Salesforce user |
Contains information about the Salesforce.com objects a Salesforce user can access. There is one row for each object a Salesforce user can access. Some of the columns in this view are: ObjectName—name of the object IsCreatable—is the object creatable? IsDeletable—is the object deletable? IsUpdatable—is the object updatable? |
| SALESFORCE_FieldsPerObject SELECT * FROM SALESFORCE_FieldsPerObject WHERE ObjectName = ‘Account’ NOTE: WHERE clause on ObjectName is required. ObjectName is the name of a Salesforce object. |
Contains information about a Salesforce.com object’s fields. There is one row for each field in a specified Salesforce.com object. Some of the columns in this view are: ObjectName—name of the object FieldName—name of the field IsCreateable—is the field insertable? Type—Field Type using SF terminology |
NOTE: if you see an error similar to this
"Error: EXCEED_ID_LIMIT: EntityDefinition does not support queryMore(), use LIMIT to restrict the results to a single batch".
use SF_SysObjectTables as an alternative stored procedure.