db_data_tracker.ps1

# ------------------------------------------------------------------------------------------------
# SQL SERVER INVENTORY SCRIPT (Using native .NET Provider: SqlClient) - MULTI-INSTANCE SUPPORT
# Generates two CSV files: one for tables and one for columns, including Server Name and Version.
# ------------------------------------------------------------------------------------------------

$SqlServerInstances = @(
    "Your_Server_1\INSTANCE_NAME_1",
    "Yuour_Server_2\INSTANCE_NAME_2"
)
 
$DatabaseName = "master"
$UseWindowsAuth = $true 

# Uncomment these variable only if the $UseWindowsAuth is not true
#$SqlUser = "user_sql" 
#$SqlPass = "pass" 

$Timestamp = Get-Date -Format yyyyMMdd_HHmmss
$TableOutputFile = "C:\InventarioBD\SQLServer_Inventory_Tables_$Timestamp.csv"
$ColumnOutputFile = "C:\InventarioBD\SQLServer_Inventory_Columns_$Timestamp.csv"
$LogFile = "C:\InventarioBD\SQLServer_Inventory_$Timestamp.log"

$GlobalTableInventory = @()
$GlobalColumnInventory = @()

Add-Type -AssemblyName System.Data

function Write-InventoryLog {
    param([string]$Message)
    $Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    "$Timestamp - $Message" | Out-File -FilePath $LogFile -Append
}

function Invoke-SqlClientQuery {
    param(
        [Parameter(Mandatory=$true)][string]$ServerInstance,
        [Parameter(Mandatory=$true)][string]$CurrentDB, 
        [Parameter(Mandatory=$true)][string]$Query,
        [Parameter(Mandatory=$false)][switch]$IsCountQuery
    )
    
    if ($global:UseWindowsAuth) {
        $ConnectionString = "Server=$ServerInstance;Database=$CurrentDB;Integrated Security=True;Connection Timeout=10;"
    } else {
        $ConnectionString = "Server=$ServerInstance;Database=$CurrentDB;User ID=$global:SqlUser;Password=$global:SqlPass;Connection Timeout=10;"
    }
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString

    try {
        $SqlConnection.Open()
        $SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SqlConnection)
        
        if ($IsCountQuery) {
            return $SqlCommand.ExecuteScalar()
        }
        
        $SqlReader = $SqlCommand.ExecuteReader()
        $results = @()
        while ($SqlReader.Read()) {
            $row = New-Object PSObject
            $SqlReader.GetSchemaTable().Rows | ForEach-Object {
                $propertyName = $_.ColumnName
                $propertyValue = $SqlReader.Item($propertyName)
                $row | Add-Member -MemberType NoteProperty -Name $propertyName -Value $propertyValue -Force
            }
            $results += $row
        }
        $SqlReader.Close()
        return $results

    } catch {
        return "ERROR: Connection or Query failed. Full Message: $($_.Exception.Message)"
    } finally {
        if ($SqlConnection -ne $null -and $SqlConnection.State -eq [System.Data.ConnectionState]::Open) {
            $SqlConnection.Close()
        }
    }
}

$OutputDir = Split-Path $TableOutputFile -Parent
if (-not (Test-Path $OutputDir)) {
    New-Item -Path $OutputDir -ItemType Directory | Out-Null
}
Write-InventoryLog "--- Inventory started for all defined SQL Server instances ---"

foreach ($ServerInstanceName in $SqlServerInstances) {
    
    Write-Host "`n========================================================" -ForegroundColor White
    Write-Host "STARTING INVENTORY FOR SERVER: $ServerInstanceName" -ForegroundColor Cyan
    Write-Host "========================================================" -ForegroundColor White
    Write-InventoryLog "Processing Server: $ServerInstanceName"

    $VersionQuery = "SELECT @@VERSION AS Version"
    $ServerInfo = Invoke-SqlClientQuery -ServerInstance $ServerInstanceName -CurrentDB $DatabaseName -Query $VersionQuery

    if ($ServerInfo -is [string] -and $ServerInfo.StartsWith("ERROR:")) {
        Write-Error "Server connection failed for $ServerInstanceName. Check log file for details."
        Write-InventoryLog "FATAL: Server connection failed to $ServerInstanceName. Error: $($ServerInfo)"
        
        $GlobalTableInventory += New-Object PSObject -Property @{
            Version = "N/A"; ServerName = $ServerInstanceName; DatabaseName = $DatabaseName; Status = "Server Disconnected";
            HasData = "N/A"; TableType = "N/A"; TableName = "N/A"; SchemaName = "N/A"
        }
        continue
    }

    $SqlServerVersion = ($ServerInfo[0].Version.Split("`n"))[0].Trim() 
    Write-Host "Found SQL Server Version: $SqlServerVersion" -ForegroundColor Yellow
    Write-InventoryLog "SUCCESS: Server $ServerInstanceName connected. Version: $SqlServerVersion"

    $DBListQuery = "SELECT name FROM sys.databases WHERE database_id > 4 AND state_desc = 'ONLINE';" 
    $UserDatabases = Invoke-SqlClientQuery -ServerInstance $ServerInstanceName -CurrentDB $DatabaseName -Query $DBListQuery

    if ($UserDatabases -is [string] -and $UserDatabases.StartsWith("ERROR:")) {
        Write-Error "Failed to retrieve database list for $ServerInstanceName."
        Write-InventoryLog "ERROR: Failed to retrieve database list from $DatabaseName on $ServerInstanceName. Error: $($UserDatabases)"
        continue
    }

    foreach ($DB in $UserDatabases) {
        $CurrentDBName = $DB.name
        Write-Host "  -> Processing Database: $CurrentDBName" -ForegroundColor Green

        $TablesQuery = @"
        SELECT
            t.TABLE_SCHEMA AS SchemaName,
            t.TABLE_NAME AS TableName,
            t.TABLE_TYPE AS TableType
        FROM
            INFORMATION_SCHEMA.TABLES t
        WHERE
            t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'cdc')
        ORDER BY SchemaName, TableName;
"@

        $Tables = Invoke-SqlClientQuery -ServerInstance $ServerInstanceName -CurrentDB $CurrentDBName -Query $TablesQuery
        
        if ($Tables -is [string] -and $Tables.StartsWith("ERROR:")) {
            Write-Warning "   Skipping database $CurrentDBName on $ServerInstanceName due to error. Details logged."
            Write-InventoryLog "ERROR: Skipping database $CurrentDBName on $ServerInstanceName. Likely permissions issue. Error: $($Tables)"
            
            $GlobalTableInventory += New-Object PSObject -Property @{
                Version = $SqlServerVersion; ServerName = $ServerInstanceName; DatabaseName = $CurrentDBName; Status = "Access Denied";
                HasData = "N/A"; TableType = "N/A"; TableName = "N/A"; SchemaName = "N/A"
            }
            continue 
        }

        $ColumnsQuery = @"
        SELECT
            '$SqlServerVersion' AS Version,
            '$ServerInstanceName' AS ServerName,
            '$CurrentDBName' AS DatabaseName,
            c.TABLE_SCHEMA AS SchemaName,
            c.TABLE_NAME AS TableName,
            c.COLUMN_NAME AS ColumnName,
            c.ORDINAL_POSITION AS OrdinalPosition,
            c.DATA_TYPE AS DataType,
            c.CHARACTER_MAXIMUM_LENGTH AS MaxLength,
            c.IS_NULLABLE AS IsNullable
        FROM
            INFORMATION_SCHEMA.COLUMNS c
        WHERE
            c.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'cdc')
        ORDER BY SchemaName, TableName, OrdinalPosition;
"@
        $Columns = Invoke-SqlClientQuery -ServerInstance $ServerInstanceName -CurrentDB $CurrentDBName -Query $ColumnsQuery
        
        if ($Columns -is [string] -and $Columns.StartsWith("ERROR:")) {
            Write-InventoryLog "WARNING: Could not retrieve column details for $CurrentDBName on $ServerInstanceName. Error: $($Columns)"
        } else {
            $GlobalColumnInventory += $Columns
        }

        foreach ($Table in $Tables) {
            $Schema = $Table.SchemaName
            $TableName = $Table.TableName
            $TableType = $Table.TableType
            $Status = "Connected"
            $HasData = "N/A"

            if ($TableType -eq 'BASE TABLE') {
                $CountQuery = "SELECT COUNT_BIG(*) FROM [$Schema].[$TableName];"
                $RowCount = Invoke-SqlClientQuery -ServerInstance $ServerInstanceName -CurrentDB $CurrentDBName -Query $CountQuery -IsCountQuery
                
                if ($RowCount -is [string] -and $RowCount.StartsWith("ERROR:")) {
                    Write-InventoryLog "WARNING: Could not count rows in $CurrentDBName.$Schema.$TableName on $ServerInstanceName. Skipping row count. Error: $($RowCount)"
                    $Status = "Connected (No Count Permission)"
                } else {
                    $HasData = if ($RowCount -gt 0) { "Yes" } else { "No" }
                }
            }
            
            $TableObject = New-Object PSObject -Property @{
                Version = $SqlServerVersion;
                ServerName = $ServerInstanceName;
                DatabaseName = $CurrentDBName;
                Status = $Status;
                TableType = $TableType; 
                SchemaName = $Schema;
                TableName = $TableName;
                HasData = $HasData;
            }
            
            $GlobalTableInventory += $TableObject
        }
    }
}

if ($GlobalTableInventory.Count -gt 0) {
    
    $TableCsvProperties = @(
        'Version', 
        'ServerName', 
        'DatabaseName', 
        'Status', 
        'TableType', 
        'SchemaName', 
        'TableName',
        'HasData'
    )
    $GlobalTableInventory | Select-Object -Property $TableCsvProperties | Export-Csv -Path $TableOutputFile -NoTypeInformation -Delimiter ','
    Write-InventoryLog "SUCCESS: Tables inventory exported to $TableOutputFile."
    Write-Host "`n✅ Tables Inventory exported to: $TableOutputFile" -ForegroundColor Green

    if ($GlobalColumnInventory.Count -gt 0) {
        $ColumnCsvProperties = @(
            'Version', 
            'ServerName', 
            'DatabaseName',
            'SchemaName',
            'TableName',
            'ColumnName',
            'OrdinalPosition',
            'DataType',
            'MaxLength',
            'IsNullable'
        )
        $GlobalColumnInventory | Select-Object -Property $ColumnCsvProperties | Export-Csv -Path $ColumnOutputFile -NoTypeInformation -Delimiter ','
        Write-InventoryLog "SUCCESS: Columns inventory exported to $ColumnOutputFile."
        Write-Host "✅ Columns Inventory exported to: $ColumnOutputFile" -ForegroundColor Green
    } else {
        Write-Warning "⚠️ Warning: No column records were found to export."
        Write-InventoryLog "WARNING: No column records were found to export."
    }

    Write-InventoryLog "--- Inventory finished. Total table records: $($GlobalTableInventory.Count) ---"
    Write-Host "Check log file for errors: $LogFile" -ForegroundColor Yellow

} else {
    Write-Warning "`n⚠️ Warning: No table records were found to export."
    Write-InventoryLog "WARNING: No table records were found to export."
}