How to Retrieve Environment Variable Values Using SQL 4 CDS in XrmToolBox?

Environment Variables are widely used in Microsoft Dataverse and Power Platform solutions to store configuration values that can differ across environments.

When troubleshooting a solution or validating a deployment, we may need to quickly identify the Environment Variable Schema Name, Description, Type, Default Value, and Current Value configured in a Dataverse environment.

Instead of opening each Environment Variable individually, we can retrieve the information using the SQL 4 CDS tool in XrmToolBox.

SQL 4 CDS allows us to use standard SQL syntax to query Dataverse data and metadata.

Follow the below steps to get the Environmental Variable configuration values in SQL 4 CDS tool.

Step 1: Open XrmToolBox.

Launch XrmToolBox.

If SQL 4 CDS isn't already installed, open:

Configuration → Tool Library

Search for:

SQL 4 CDS

and install it.

Step 2: Connect to the Dataverse Environment

Connect XrmToolBox to the Dataverse environment from which you want to retrieve the Environment Variables.

For example:

Development
SIT
UAT
Production

When SQL 4 CDS opens, the connected Dataverse instance is shown in the Object Explorer.

Step 3: Open SQL 4 CDS

From the XrmToolBox Tools tab, search for:

SQL 4 CDS

Open the tool.

SQL 4 CDS provides a SQL query interface for querying data stored in Microsoft Dataverse.

Step 4: Execute the SQL Query

Copy the following query into the SQL 4 CDS query editor:

SELECT d.schemaname AS EnvironmentVariableSchemaName,
description AS Description,
typename AS Type,
d.defaultvalue AS DefaultValue,
v.value AS CurrentValue,
COALESCE(v.value, d.defaultvalue) AS EffectiveValue
FROM environmentvariabledefinition AS d
LEFT OUTER JOIN
environmentvariablevalue AS v
ON d.environmentvariabledefinitionid = v.environmentvariabledefinitionid

Click Execute.

Step 5: Review the Results

The query provides a consolidated view of all the Environment Variable configurations:

EnvironmentVariableSchemaName: Schema name of the Environment Variable
Description: Description of the Environment Variable
Type: Environment Variable type
DefaultValue: Default value defined for the Environment Variable
CurrentValue: Value stored for the Environment Variable in the connected Dataverse environment
EffectiveValue: Final value which is used in real time.

Use the below query to get the required Environment Variables values.

SELECT d.schemaname AS EnvironmentVariableSchemaName,
description AS Description,
typename AS Type,
d.defaultvalue AS DefaultValue,
v.value AS CurrentValue,
COALESCE(v.value, d.defaultvalue) AS EffectiveValue
FROM environmentvariabledefinition AS d
LEFT OUTER JOIN
environmentvariablevalue AS v
ON d.environmentvariabledefinitionid = v.environmentvariabledefinitionid
WHERE d.schemaname IN ('new_TestEnvVariable');

Practical scenarios:

You can use this query when:

Validating Environment Variables after a solution deployment
Troubleshooting configuration differences between DEV, SIT, UAT and Production
Checking whether an Environment Variable has a current value
Identifying variables that are relying on default values
Reviewing Environment Variable configuration from one place
Comparing configuration between Dataverse environments
Troubleshooting Power Apps or Power Automate solutions that depend on Environment Variables

Conclusion

Using SQL 4 CDS in XrmToolBox provides a convenient way to retrieve Dataverse Environment Variable configurations using familiar SQL syntax.