This script gets the columns info (cid, type, notnull, ...) of the SQLite tables that are contained in the specified DB file.

Get-SqliteTableColumns.ps1

# 
# File: Get-SqliteTableColumns.ps1
# 
# Author: Akira Sugiura ([email protected])
# 
# 
# Copyright (c) 2024 Akira Sugiura
#  
#  This software is MIT License.
#  
#  Permission is hereby granted, free of charge, to any person obtaining a copy
#  of this software and associated documentation files (the "Software"), to deal
#  in the Software without restriction, including without limitation the rights
#  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
#  copies of the Software, and to permit persons to whom the Software is
#  furnished to do so, subject to the following conditions:
#  
#  The above copyright notice and this permission notice shall be included in
#  all copies or substantial portions of the Software.
#  
#  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
#  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
#  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
#  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
#  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
#  THE SOFTWARE.
#

<#
    .SYNOPSIS
        Gets the columns of the SQLite tables that are contained in the specified DB file.

    .PARAMETER  Database
        A file path of DB file you want to list columns.

    .PARAMETER  OutputFile
        A file path to output column info. It has a CSV format.

    .PARAMETER  Sqlite3
        A file path for Command Line Shell For SQLite.

    .EXAMPLE
        .\Get-SqliteTableColumns.ps1 -Database "C:\Temp\test.db" -OutputFile result.csv -Sqlite3 "C:\SQLite\sqlite3.exe"
        
        DESCRIPTION
        -----------
        This example gets the database `C:\Temp\test.db`, and outputs the columns info to `result.csv`.

    .INPUTS
        System.Void

    .OUTPUTS
        System.Void

#>

[CmdletBinding()]
param (
    [Parameter(Mandatory = $True)]
    [string]
    $Database, 
    
    [Parameter(Mandatory = $True)]
    [string]
    $OutputFile,
    
    [Parameter(Mandatory = $True)]
    [string]
    $Sqlite3
)

Set-Content -Path $OutputFile -Value '"cid","name","type","notnull","dflt_value","pk","TableName"' -Encoding utf8
$tables = (& $Sqlite3 $Database ".tables") -split '\s+' | ? { ![string]::IsNullOrEmpty($_) }

foreach ($table in $tables) {
    $query = ".headers on", "PRAGMA table_info($table);"
    $columns = & $Sqlite3 $Database $query | ConvertFrom-Csv -Delimiter "|"
    $columns | Add-Member -MemberType NoteProperty -Name 'TableName' -Value $table

    $columns | Export-Csv -Path $OutputFile -Append -NoTypeInformation -Encoding utf8
}