Public/Get-PdqDatabaseTable.ps1
|
<#
.SYNOPSIS Retrieves information about database tables. .INPUTS None. .OUTPUTS System.Management.Automation.PSCustomObject System.Object[] System.Collections.Hashtable .EXAMPLE Get-PdqDatabaseTable -Product 'Deploy' Outputs all tables from Deploy's database. .EXAMPLE (Get-PdqDatabaseTable -Product 'Inventory' -Name 'Services').Columns Outputs the columns of the Services table. #> function Get-PdqDatabaseTable { [CmdletBinding()] param ( # The names of the tables you would like to look up. [String[]]$Name, [Parameter(Mandatory = $true)] [ValidateSet('Deploy', 'Inventory')] # The PDQ application you would like to execute this function against. [String]$Product, # Output a hashtable instead of an array of PSCustomObjects. [Switch]$AsHashtable, # The path to the currently active database will be retrieved by default. # You can use this parameter if you wish to run this function against a different database. [String]$DatabasePath ) # https://stackoverflow.com/a/50548508 $Query = @" SELECT m.name AS TableName , m.type AS Type , p.name AS ColumnName FROM sqlite_master m LEFT OUTER JOIN pragma_table_info((m.name)) p ON m.name <> p.name ORDER BY TableName, ColumnName COLLATE NOCASE ; "@ try { $CloseConnection = Open-PdqSqlConnection -Product $Product -DatabasePath $DatabasePath # Get all tables. $AllTables = @{} Invoke-PdqSqlQuery -Product $Product -Query $Query -Stream | Group-Object -Property 'TableName' | ForEach-Object { $TableData = $_ # Skip tables that aren't in $Name. if ( (-not $Name) -or ($TableData.Name -in $Name) ) { $TableValue = @{ 'Type' = ($TableData.Group.Type | Group-Object).Name 'Columns' = $TableData.Group.ColumnName } $AllTables.Add($TableData.Name, $TableValue) } } # Make sure all entries in $Name are valid table names. foreach ( $DesiredName in $Name ) { if ( -not $AllTables.ContainsKey($DesiredName) ) { throw "'$DesiredName' does not exist in this database." } } # Output the tables. if ( $AsHashtable ) { $AllTables } else { # I wanted to use an ordered dictionary, but those don't have the ContainsKey method that I use in several places. $AllTables.GetEnumerator() | Sort-Object -Property 'Key' | ForEach-Object { $Output = @{ 'Name' = $_.Key } $Output += $_.Value [PSCustomObject]$Output } } } finally { Close-PdqSqlConnection -Product $Product -CloseConnection $CloseConnection } } |