Schema Analyzer

Schema Analyzer

Produce a complete schema analysis and comparison document for a data migration. This is the first deliverable in a migration engagement. The output drives field mapping, transformation design, and complexity estimation for the rest of the project.

Source Schema Extraction

Extract the schema from the source system using the method appropriate for the system type:

SQL Server Source

-- Extract full table schema
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    tp.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS Precision,
    c.scale AS Scale,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    dc.definition AS DefaultValue,
    ep.value AS ColumnDescription
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id 
    AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
WHERE t.type = 'U'
ORDER BY t.name, c.column_id;

-- Extract foreign key relationships
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ParentTable,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
    OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY ParentTable, ParentColumn;

Dataverse Source

Use the Dataverse Web API to extract entity metadata:

GET [org-url]/api/data/v9.2/EntityDefinitions?$select=LogicalName,DisplayName,Description
GET [org-url]/api/data/v9.2/EntityDefinitions(LogicalName='account')/Attributes?$select=LogicalName,AttributeType,RequiredLevel,DisplayName

SharePoint List Source

Export list schema via PowerShell:

Connect-PnPOnline -Url $siteUrl -Interactive
$list = Get-PnPList -Identity "ListName"
$fields = Get-PnPField -List "ListName"
$fields | Select-Object InternalName, TypeAsString, Required, MaxLength, DefaultValue |
  Export-Csv ".\sharepoint-schema.csv" -NoTypeInformation

CSV / Excel Source

Document the schema by inspection:

• Column headers (as-is from the file)
• Inferred data type (text, number, date, boolean)
• Presence of null/empty values (sample 100 rows)
• Distinct value count (for potential code/lookup fields)
• File encoding and delimiter

Source Schema Documentation

For each entity/table being migrated, produce:

Entity: [Table/List name] Record count: [Approximate count] Primary key: [Column name and type] System of record: [System name]

Target Schema Documentation

Document the destination system's schema for the same entities:

Entity: [Destination entity name] System: [Destination system name] Write method: REST API / SQL INSERT / Dataverse upsert / SharePoint list item

Schema Comparison

Produce a side-by-side comparison identifying all differences:

Coverage Analysis

Source fields with no destination mapping (document each — confirm discard is intentional):

Destination required fields with no source (must resolve before migration can proceed):

Data Type Mismatches

Field Length Mismatches

Relationship Mapping

Business Logic Identification

Document any business logic embedded in the source system that must be replicated in migration:

Migration Complexity Scoring

Rate each entity/table by migration complexity:

Entity complexity table:

Output Format

Deliver as:

1. Source schema extraction method and queries used
2. Source schema documentation (one table per entity)
3. Target schema documentation (one table per entity)
4. Schema comparison tables:
   • Unmapped source fields
   • Destination required fields with no source
   • Data type mismatches
   • Field length mismatches
   • Relationship mapping
5. Business logic catalog
6. Complexity scoring table with rationale
7. Open questions (items requiring client confirmation before field mapping proceeds)
8. Next steps (recommended order: data profiling → field mapping → transformation design → test migration)