# ============================================
# Compare Tables & Columns Across All Databases
# ============================================

# SQL Server Details
$server   = "45.114.141.105,21443"
$username = "sqluser"
$password = '!n$Id3cpm4783#'

# Output Files
$outputFile        = "C:\Temp\Table_Column_Mismatch_Report.csv"
$distinctTableFile = "C:\Temp\DistinctTables.csv"

# ============================================
# Load SQL Server Module
# ============================================

Import-Module SqlServer -ErrorAction Stop

# ============================================
# Create Temp Folder If Not Exists
# ============================================

$tempFolder = "C:\Temp"

if (!(Test-Path $tempFolder))
{
    New-Item -ItemType Directory -Path $tempFolder | Out-Null
}

Write-Host ""
Write-Host "============================================" -ForegroundColor Cyan
Write-Host "Connecting To SQL Server..." -ForegroundColor Cyan
Write-Host "Server : $server" -ForegroundColor Cyan
Write-Host "============================================" -ForegroundColor Cyan
Write-Host ""

# ============================================
# Get All Online User Databases
# ============================================

try
{
    $databases = Invoke-Sqlcmd `
        -ServerInstance $server `
        -Database master `
        -Username $username `
        -Password $password `
        -TrustServerCertificate `
        -ConnectionTimeout 60 `
        -QueryTimeout 0 `
        -Query "
SELECT name
FROM sys.databases
WHERE state = 0
AND database_id > 4
ORDER BY name
"

    Write-Host "Databases Loaded Successfully" -ForegroundColor Green
}
catch
{
    Write-Host ""
    Write-Host "Failed To Connect SQL Server" -ForegroundColor Red
    Write-Host $_.Exception.Message -ForegroundColor Red
    exit
}

# ============================================
# Store All Table/Column Data
# ============================================

$allTables = @()

# ============================================
# Loop Through Each Database
# ============================================

foreach ($db in $databases)
{
    $dbName = $db.name

    Write-Host ""
    Write-Host "Checking DB : $dbName" -ForegroundColor Yellow

    $query = @"
SELECT
    '$dbName' AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.column_id AS ColumnOrder,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS PrecisionValue,
    c.scale AS ScaleValue,
    c.is_nullable AS IsNullable
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
INNER JOIN sys.types ty
    ON c.user_type_id = ty.user_type_id
ORDER BY s.name, t.name, c.column_id
"@

    try
    {
        $result = Invoke-Sqlcmd `
            -ServerInstance $server `
            -Database $dbName `
            -Username $username `
            -Password $password `
            -TrustServerCertificate `
            -ConnectionTimeout 60 `
            -QueryTimeout 0 `
            -Query $query

        $allTables += $result

        Write-Host "SUCCESS : $dbName" -ForegroundColor Green
    }
    catch
    {
        Write-Host "FAILED  : $dbName" -ForegroundColor Red
        Write-Host $_.Exception.Message -ForegroundColor Red
    }
}

# ============================================
# Get Distinct Tables
# ============================================

$distinctTables = $allTables |
    Select-Object SchemaName, TableName -Unique

Write-Host ""
Write-Host "============================================" -ForegroundColor Cyan
Write-Host "Total Distinct Tables Found : $($distinctTables.Count)" -ForegroundColor Cyan
Write-Host "============================================" -ForegroundColor Cyan
Write-Host ""

# ============================================
# Show Distinct Tables In Console
# ============================================

$distinctTables |
    Sort-Object SchemaName, TableName |
    Format-Table -AutoSize

# ============================================
# Export Distinct Tables
# ============================================

$distinctTables |
    Sort-Object SchemaName, TableName |
    Export-Csv $distinctTableFile -NoTypeInformation

Write-Host ""
Write-Host "Distinct Tables Exported :" -ForegroundColor Green
Write-Host $distinctTableFile -ForegroundColor Green

# ============================================
# Compare Table Structures
# ============================================

$mismatchResults = @()

$groupedTables = $allTables | Group-Object SchemaName, TableName

foreach ($tableGroup in $groupedTables)
{
    $rows = $tableGroup.Group

    # Group Same Table By Database
    $dbGroups = $rows | Group-Object DatabaseName

    $dbColumnMap = @{}

    foreach ($dbGroup in $dbGroups)
    {
        $dbName = $dbGroup.Name

        # Build Structure Signature
        $columns = $dbGroup.Group |
            Sort-Object ColumnOrder |
            ForEach-Object {

                "$($_.ColumnName)|" +
                "$($_.DataType)|" +
                "$($_.MaxLength)|" +
                "$($_.PrecisionValue)|" +
                "$($_.ScaleValue)|" +
                "$($_.IsNullable)"
            }

        $dbColumnMap[$dbName] = ($columns -join ",")
    }

    # Compare Structures
    $uniqueStructures = $dbColumnMap.Values | Select-Object -Unique

    # If Structure Different
    if ($uniqueStructures.Count -gt 1)
    {
        foreach ($dbName in $dbColumnMap.Keys)
        {
            $tableInfo = $rows | Select-Object -First 1

            $mismatchResults += [PSCustomObject]@{
                DatabaseName    = $dbName
                SchemaName      = $tableInfo.SchemaName
                TableName       = $tableInfo.TableName
                ColumnStructure = $dbColumnMap[$dbName]
            }
        }
    }
}

# ============================================
# Export Mismatch Report
# ============================================

if ($mismatchResults.Count -gt 0)
{
    $mismatchResults |
        Sort-Object SchemaName, TableName, DatabaseName |
        Export-Csv $outputFile -NoTypeInformation

    Write-Host ""
    Write-Host "============================================" -ForegroundColor Green
    Write-Host "Mismatch Report Generated Successfully" -ForegroundColor Green
    Write-Host "============================================" -ForegroundColor Green

    Write-Host ""
    Write-Host "Mismatch Report File :" -ForegroundColor Green
    Write-Host $outputFile -ForegroundColor Green
}
else
{
    Write-Host ""
    Write-Host "============================================" -ForegroundColor Green
    Write-Host "No Table Structure Mismatches Found" -ForegroundColor Green
    Write-Host "============================================" -ForegroundColor Green
}
***************
***************
COULNM DO NOT EXITS
***************
***************

# ============================================
# Compare ONLY Mismatch Columns Across All DBs
# ============================================

# SQL Server Details
$server   = "45.114.141.105,21443"
$username = "sqluser"
$password = '!n$Id3cpm4783#'

# Output Files
$outputFile        = "C:\Temp\Table_Column_Mismatch_Report.csv"
$distinctTableFile = "C:\Temp\DistinctTables.csv"

# ============================================
# Load SQL Server Module
# ============================================

Import-Module SqlServer -ErrorAction Stop

# ============================================
# Create Temp Folder If Not Exists
# ============================================

$tempFolder = "C:\Temp"

if (!(Test-Path $tempFolder))
{
    New-Item -ItemType Directory -Path $tempFolder | Out-Null
}

Write-Host ""
Write-Host "============================================" -ForegroundColor Cyan
Write-Host "Connecting To SQL Server..." -ForegroundColor Cyan
Write-Host "Server : $server" -ForegroundColor Cyan
Write-Host "============================================" -ForegroundColor Cyan
Write-Host ""

# ============================================
# Get All Online User Databases
# ============================================

try
{
    $databases = Invoke-Sqlcmd `
        -ServerInstance $server `
        -Database master `
        -Username $username `
        -Password $password `
        -TrustServerCertificate `
        -ConnectionTimeout 60 `
        -QueryTimeout 0 `
        -Query "
SELECT name
FROM sys.databases
WHERE state = 0
AND database_id > 4
ORDER BY name
"

    Write-Host "Databases Loaded Successfully" -ForegroundColor Green
}
catch
{
    Write-Host ""
    Write-Host "Failed To Connect SQL Server" -ForegroundColor Red
    Write-Host $_.Exception.Message -ForegroundColor Red
    exit
}

# ============================================
# Store All Table/Column Data
# ============================================

$allTables = @()

# ============================================
# Loop Through Each Database
# ============================================

foreach ($db in $databases)
{
    $dbName = $db.name

    Write-Host ""
    Write-Host "Checking DB : $dbName" -ForegroundColor Yellow

    $query = @"
SELECT
    '$dbName' AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.column_id AS ColumnOrder,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS PrecisionValue,
    c.scale AS ScaleValue,
    c.is_nullable AS IsNullable
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
INNER JOIN sys.types ty
    ON c.user_type_id = ty.user_type_id
ORDER BY s.name, t.name, c.column_id
"@

    try
    {
        $result = Invoke-Sqlcmd `
            -ServerInstance $server `
            -Database $dbName `
            -Username $username `
            -Password $password `
            -TrustServerCertificate `
            -ConnectionTimeout 60 `
            -QueryTimeout 0 `
            -Query $query

        $allTables += $result

        Write-Host "SUCCESS : $dbName" -ForegroundColor Green
    }
    catch
    {
        Write-Host "FAILED  : $dbName" -ForegroundColor Red
        Write-Host $_.Exception.Message -ForegroundColor Red
    }
}

# ============================================
# Get Distinct Tables
# ============================================

$distinctTables = $allTables |
    Select-Object SchemaName, TableName -Unique

Write-Host ""
Write-Host "============================================" -ForegroundColor Cyan
Write-Host "Total Distinct Tables Found : $($distinctTables.Count)" -ForegroundColor Cyan
Write-Host "============================================" -ForegroundColor Cyan
Write-Host ""

# ============================================
# Export Distinct Tables
# ============================================

$distinctTables |
    Sort-Object SchemaName, TableName |
    Export-Csv $distinctTableFile -NoTypeInformation

Write-Host "Distinct Tables Exported :" -ForegroundColor Green
Write-Host $distinctTableFile -ForegroundColor Green

# ============================================
# Compare ONLY Mismatch Columns
# ============================================

$mismatchResults = @()

$groupedTables = $allTables | Group-Object SchemaName, TableName

foreach ($tableGroup in $groupedTables)
{
    $rows = $tableGroup.Group

    # Get all DBs for this table
    $dbGroups = $rows | Group-Object DatabaseName

    # Get all unique column names
    $allColumnNames = $rows.ColumnName | Select-Object -Unique

    foreach ($columnName in $allColumnNames)
    {
        $columnData = $rows | Where-Object {
            $_.ColumnName -eq $columnName
        }

        $columnStructures = @{}

        foreach ($dbGroup in $dbGroups)
        {
            $dbName = $dbGroup.Name

            $col = $columnData | Where-Object {
                $_.DatabaseName -eq $dbName
            }

            if ($col)
            {
                $structure =
                    "$($col.DataType)|" +
                    "$($col.MaxLength)|" +
                    "$($col.PrecisionValue)|" +
                    "$($col.ScaleValue)|" +
                    "$($col.IsNullable)"
            }
            else
            {
                $structure = "COLUMN_NOT_EXISTS"
            }

            $columnStructures[$dbName] = $structure
        }

        # Compare structures
        $uniqueStructures = $columnStructures.Values | Select-Object -Unique

        # Only mismatch columns
        if ($uniqueStructures.Count -gt 1)
        {
            foreach ($dbName in $columnStructures.Keys)
            {
                $tableInfo = $rows | Select-Object -First 1

                $mismatchResults += [PSCustomObject]@{
                    DatabaseName = $dbName
                    SchemaName   = $tableInfo.SchemaName
                    TableName    = $tableInfo.TableName
                    ColumnName   = $columnName
                    Structure    = $columnStructures[$dbName]
                }
            }
        }
    }
}

# ============================================
# Export Mismatch Report
# ============================================

if ($mismatchResults.Count -gt 0)
{
    $mismatchResults |
        Sort-Object SchemaName, TableName, ColumnName, DatabaseName |
        Export-Csv $outputFile -NoTypeInformation

    Write-Host ""
    Write-Host "============================================" -ForegroundColor Green
    Write-Host "Mismatch Report Generated Successfully" -ForegroundColor Green
    Write-Host "============================================" -ForegroundColor Green

    Write-Host ""
    Write-Host "Mismatch Report File :" -ForegroundColor Green
    Write-Host $outputFile -ForegroundColor Green
}
else
{
    Write-Host ""
    Write-Host "============================================" -ForegroundColor Green
    Write-Host "No Column Mismatches Found" -ForegroundColor Green
    Write-Host "============================================" -ForegroundColor Green
}