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 ) $TablesQuery = @" SELECT name AS Name , type AS Type FROM sqlite_master WHERE type IN ('table', 'view') ORDER BY name COLLATE NOCASE; "@ try { $CloseConnection = Open-PdqSqlConnection -Product $Product -DatabasePath $DatabasePath $FoundTables = New-Object System.Collections.Generic.List[String] $HashtableOutput = @{} # Get all tables. Invoke-PdqSqlQuery -Product $Product -Query $TablesQuery -Stream | ForEach-Object { $Table = $_ # Skip tables that aren't in $Name. if ( (-not $Name) -or ($Table.Name -in $Name) ) { $FoundTables.Add($Table.Name) # Get all columns for the table. $ColumnsQuery = "pragma table_info('$($Table.name)')" $Columns = (Invoke-PdqSqlQuery -Product $Product -Query $ColumnsQuery).name | Sort-Object # Add the columns to the table object. $Table | Add-Member -MemberType 'NoteProperty' -Name 'Columns' -Value $Columns if ( $AsHashtable ) { # I don't want Name twice. $NewObject = [PSCustomObject]@{ 'Type' = $Table.Type 'Columns' = $Table.Columns } $HashtableOutput.Add($Table.Name, $NewObject) } else { $Table } } } foreach ( $DesiredName in $Name ) { if ( $DesiredName -notin $FoundTables ) { throw "'$DesiredName' does not exist in this database." } } if ( $AsHashtable ) { $HashtableOutput } } finally { Close-PdqSqlConnection -Product $Product -CloseConnection $CloseConnection } } |