Exporting Dataverse Table Schema using PowerShell

Microsoft Dataverse provides a structured way to manage business data within Power Platform. If you need to back up or migrate your table schema (metadata), you can use PowerShell to export it. This guide explains how to extract the Dataverse table schema using PowerShell.

Step 1: Prerequisites

1. Required Permissions

You must have the System Administrator or Power Platform Admin role in Dataverse.
The Microsoft Dataverse API should be enabled for your environment.

2. Install and Import PowerShell Modules

To interact with Dataverse, install and import the required PowerShell modules.

# Install Power Platform Administration module
Install-Module -Name Microsoft.PowerPlatform.Administration -Scope CurrentUser -Force

# Install Dataverse Client module
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import modules
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

Step 2: Connect to Dataverse

Option 1: Interactive Login (Prompt for Credentials)

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

A sign-in window will appear, prompting you to log in with your Microsoft 365 admin credentials.

Option 2: Using Service Principal (App Registration)

For automated scripts, use an Azure AD App Registration.

# Define connection details
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Create authentication context
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Step 3: Retrieve Table Schema from Dataverse

To export the schema, retrieve the metadata of a specific table.

# Define the table name
$tableName = "new_CustomerData"  # Replace with your table’s logical name

# Retrieve table metadata
$tableMetadata = Get-CdsEntity -Connection $connection -EntityLogicalName $tableName

# Display table schema
$tableMetadata | Select-Object LogicalName, DisplayName, Description, OwnershipType

Step 4: Retrieve Column Metadata

Extract details about all columns (attributes) of a specific table.

# Retrieve all columns for the specified table
$columns = Get-CdsTableColumn -Connection $connection -EntityLogicalName $tableName

# Display columns
$columns | Select-Object LogicalName, DisplayName, AttributeType, IsRequired

Step 5: Export Schema to a CSV File

1. Export Table Schema

# Define file path
$schemaFilePath = "C:\Dataverse_Schema\TableSchema.csv"

# Export table metadata to CSV
$tableMetadata | Select-Object LogicalName, DisplayName, Description, OwnershipType | Export-Csv -Path $schemaFilePath -NoTypeInformation

Write-Host "Table schema exported successfully to $schemaFilePath"

2. Export Column Schema

# Define file path for columns
$columnFilePath = "C:\Dataverse_Schema\ColumnSchema.csv"

# Export column metadata to CSV
$columns | Select-Object LogicalName, DisplayName, AttributeType, IsRequired | Export-Csv -Path $columnFilePath -NoTypeInformation

Write-Host "Column schema exported successfully to $columnFilePath"

Step 6: Export Schema to JSON

You can also export schema in JSON format for use in other applications.

# Define file path
$jsonFilePath = "C:\Dataverse_Schema\Schema.json"

# Convert table and column metadata to JSON
$schemaData = @{
    Table = $tableMetadata | Select-Object LogicalName, DisplayName, Description, OwnershipType
    Columns = $columns | Select-Object LogicalName, DisplayName, AttributeType, IsRequired
}

# Export JSON
$schemaData | ConvertTo-Json -Depth 2 | Set-Content -Path $jsonFilePath

Write-Host "Schema exported successfully to $jsonFilePath"

Step 7: Export All Tables and Columns in Dataverse

If you need to extract all tables and columns in your Dataverse environment, run:

# Retrieve all tables
$allTables = Get-CdsEntity -Connection $connection

# Export all tables to CSV
$allTables | Select-Object LogicalName, DisplayName, Description, OwnershipType | Export-Csv -Path "C:\Dataverse_Schema\AllTables.csv" -NoTypeInformation

Write-Host "All tables exported successfully."

# Export columns for all tables
foreach ($table in $allTables) {
    $columns = Get-CdsTableColumn -Connection $connection -EntityLogicalName $table.LogicalName
    $columns | Select-Object @{N="Table";E={$table.DisplayName}}, LogicalName, DisplayName, AttributeType, IsRequired | Export-Csv -Path "C:\Dataverse_Schema\AllColumns.csv" -Append -NoTypeInformation
}

Write-Host "All columns exported successfully."

Step 8: Verify the Exported Files

Go to C:\Dataverse_Schema\ and verify that the following files are generated:
TableSchema.csv (Contains table-level details)
ColumnSchema.csv (Contains column details of a specific table)
Schema.json (Contains schema in JSON format)
AllTables.csv (Contains details of all tables)
AllColumns.csv (Contains all columns from all tables)

Step 9: Disconnect from Dataverse

Once the export process is completed, disconnect from Dataverse.

Disconnect-CdsService -Connection $connection
Write-Host "Disconnected from Dataverse."