SLAM_Alpha_query_tooling

Overview

Executes a SOQL query against the Salesforce Tooling API and writes the results to a SQL Server table.

The Tooling API exposes metadata objects not available through the standard query API such as EntityParticle (atomic field definitions), EntityDefinition (object definitions), FieldDefinition, ApexClass and many others. Use this procedure when you need access to org metadata beyond what SLAM_describe_objects and SLAM_data_dictionary provide.

You can pass a full SOQL query, use SELECT * to retrieve all fields, or pass just the Tooling API object name to automatically query all fields. The procedure handles pagination automatically and flattens any nested relationship fields in the response.

For more info about the Tooling API, refer to Salesforce developer documentation

Parameters

Name	Type	Default	Description
@query	NVARCHAR(MAX)	Required	A Tooling API object name (e.g. 'ApexComponent') or SOQL query string. When just an object name is passed, queries all records & fields
@table	VARCHAR(128)	Required	Target SQL table name for results
@exists_action	VARCHAR(20)	NULL	Controls the behavior when the destination table already exists. If NULL, uses the value from SLAM_Settings.default_exists_action (defaulted to 'drop' during initial setup). Valid options: 'drop' - Drop the existing object and create a new one (default) 'rename' - Rename the existing object by appending a timestamp suffix (format: _YYYYMMDDTHHMMSS_MMM) 'fail' - Throws a SQL exception if the destination object already exists

Output Table

Results are written to the table specified by @table. The table is created automatically if it does not exist. Columns follow the order of fields in your SELECT clause.

Relationship fields

Tooling API queries frequently traverse relationships using dot-notation (e.g. EntityDefinition.QualifiedApiName). The destination table will preserve this dot-notation in the column headers.

For example, the query:

SELECT QualifiedApiName, DataType, EntityDefinition.QualifiedApiName
FROM EntityParticle
WHERE EntityDefinition.QualifiedApiName = 'Account'

Produces columns: QualifiedApiName, DataType, EntityDefinition.QualifiedApiName.

note

Column names containing a dot (e.g. EntityDefinition.QualifiedApiName) must be quoted when referenced in SQL:

SELECT [EntityDefinition.QualifiedApiName] FROM Account_Fields;

Usage Examples

Query using just the object name

Pass the Tooling API object name instead of a full SOQL query. The procedure automatically builds SELECT * FROM {object}.

EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'ApexComponent',
    @table = 'Apex_Components';

Query with SELECT *

Use SELECT * with a WHERE filter to retrieve all fields for a subset of records:

EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT * FROM EntityParticle WHERE EntityDefinition.QualifiedApiName = ''Account''',
    @table = 'Account_Fields';

Query all fields on the Account object

EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT QualifiedApiName, DataType, Label, IsCreatable, IsUpdatable FROM EntityParticle WHERE EntityDefinition.QualifiedApiName = ''Account''',
    @table = 'Account_Fields';

SELECT * FROM Account_Fields;

Query all Apex classes

EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT Id, Name, Status, IsValid, LengthWithoutComments FROM ApexClass ORDER BY Name',
    @table = 'Apex_Classes';

Query with a relationship traversal field

EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT QualifiedApiName, DataType, EntityDefinition.QualifiedApiName FROM EntityParticle WHERE EntityDefinition.QualifiedApiName IN (''Account'', ''Contact'', ''Opportunity'')',
    @table = 'Fields_By_Object';

-- Reference the flattened column using square brackets
SELECT
    [EntityDefinition.QualifiedApiName] AS SObject,
    QualifiedApiName AS FieldName,
    DataType
FROM Fields_By_Object
ORDER BY [EntityDefinition.QualifiedApiName], QualifiedApiName;

Pagination and Large Result Sets

For most Tooling API objects, pagination is handled automatically. All pages are fetched and combined before writing to SQL Server.

Some objects (EntityDefinition, EntityParticle) do not support Salesforce's queryMore() pagination. An unfiltered query against these objects will return an error:

EXCEEDED_ID_LIMIT: EntityDefinition does not support queryMore()

Add a WHERE clause or LIMIT to reduce the result set:

-- Filter by object name instead of querying all objects at once
EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT QualifiedApiName, DataType FROM EntityParticle WHERE EntityDefinition.QualifiedApiName = ''Account''',
    @table = 'Account_Fields';

Tooling API Query Restrictions

caution

Some Tooling API objects enforce query restrictions that are not present on standard objects. These restrictions are enforced by Salesforce and cannot be bypassed.

Reified column filters: Certain objects require a filter on specific columns. Querying without the required filter returns an error:

-- Query:
SELECT Id, EntityDefinition.FullName, DurableId, QualifiedApiName, EntityDefinitionId, FieldDefinitionId, NamespacePrefix, DeveloperName, MasterLabel FROM EntityParticle

-- Error:
MALFORMED_QUERY: EntityParticle: a filter on a reified column is required [EntityDefinitionId, FieldDefinitionId, DurableId]

Metadata/FullName field restrictions: Objects that expose Metadata or FullName fields may fail when the query returns more than one row, or when querying non-customizable objects:

-- Query:
SELECT Id, Description, DeveloperName, FullName FROM EntityDefinition

-- Error:
UNKNOWN_EXCEPTION: Can't retrieve non-customizable CustomObject named: ContentHubRepository

To work around these restrictions, either exclude the problematic fields or add a WHERE clause targeting a specific record:

-- Exclude FullName/Metadata fields
EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT Id, Description, DeveloperName FROM EntityDefinition LIMIT 2000',
    @table = 'EntityDefinition_tooling_query';

-- Or target a single record when FullName is needed
EXEC dbo.SLAM_Alpha_query_tooling
    @query = 'SELECT Id, Description, DeveloperName, FullName FROM EntityDefinition WHERE DeveloperName = ''Account''',
    @table = 'EntityDefinition_tooling_query';

Related Procedures

• SLAM_metadata_relationships — retrieves all parent-child object relationships via the Tooling API.
• SLAM_describe_objects — retrieves the list of all Salesforce objects and their attributes via the standard describe API.
• SLAM_data_dictionary — retrieves field definitions for all (or filtered) objects via the Tooling API.
• SLAM_query — queries standard Salesforce data objects (Account, Contact, etc.).