Feb 18, 2009 article by ScriptingGuy1 -- How Can I tell which tables and columns are in a database without opening it? RRS feed

  • Question

  • Hello Sir/Madam,

    Although the above article is now 10 years old -- I have learned a lot from it and used it quite a bit just to get useful info about my collection of MS-ACCESS databases.  It is truly a very useful script.  However, I am truly lost as to how the NEWLINE function can print the various (FIELD/FIELD TYPE) pairs for each table being queried for its DB schema info.  Obviously I am missing on some fundamentals of pipeline processing or the way the Get-DatabaseSchema function -- which is the heart of this script -- works. There are two main areas that I have spent many hours trying to understand but fail to:

    (1) There is a sentence that says "We then send the table name back to the $Tables variable." followed by a box of 4 lines of code.  I struggled with understanding what this meant "send the table name back".

    (2) That section of code that loops through the Columns of each specific table completely eludes me:


    $schemaColumn | foreach-object { if ($_.Table_Name -EQ $Table) {

                              {"$($_.COLUMN_Name ) $(Get-DataType ($_.DATA_TYPE))") }

                              }  # -- end for each object

                           }  # -- end for each table.


    It is in the second line where I thought a call to the New-Line function would exist, but there isn't any.  Yet the script works perfectly fine!  I would be highly grateful for a clarification as to the fundamental principles of PS operation -- and perhaps, also the way datasets[] work -- that I know I am lacking and that's why I cannot understand the code. 

    Your advice/guidance would be highly appreciated.  Thanking you in advance.


    • Moved by Bill_Stewart Friday, March 15, 2019 5:34 PM This is not "train me in basics step-by-step in a forum" forum
    Monday, January 14, 2019 5:50 AM

All replies

  • To learn how to read and understand code I recommend starting with the following tutorial.

    Microsoft Virtual Academy - Getting Started with Microsoft PowerShell

    The article referenced is 10 years old and is not really a very good example of coding in PowerShell.  The explanation is also very vague and, as you see, a bit misleading.  The code and explanation are about how things would be done in PS V1 and we are at V5.1/6. 

    If you learn basic PowerShell you will learn about how to assign variables and how to use looping structures.  This forum is not a good palce to get personal tutorials.

    Once you  learn basic PowerShell the code and explanation will make more sense.


    Monday, January 14, 2019 6:52 AM
  • Thanks -- the tutorials you referred to are for PS 2 and 3.  I am already conversant with PS 5.1, it's just this specific script that is incomprehensible to me, most likely due to it being in PS 1.  Thanks in any case.


    Monday, January 14, 2019 8:24 AM
  • The script is incomprehensible because you are no fluent in PowerShell.  The tutorial covers WNF 3 and skips PS V1.  The scipt posted is V1 or the first release. The code is very poorly designed and uses methods required in V1.

    Here is how it would look in WMF 5.1

    Function Get-DataBaseSchema { param ( [Parameter(Mandatory=$true)] [string]$AccessFile ) Try{ Write-Verbose "Obtaining Schema for $AccessFile" $connStr = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$AccessFile;Persist Security Info=False"
            #$connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$Db" $conn = New-Object Data.OleDb.OleDbConnection($connStr) $conn.open() $conn.GetSchema('Tables') | Where{$_.Table_Type -eq 'TABLE'} | ForEach-Object{ $table = $_.TABLE_NAME Write-Host "Table Name: $table" -Fore green $conn.GetSchema('Columns') | ForEach-Object { If ($_.TABLE_NAME -eq $Table) { $msg = ' {0,-20} {1}' -f $_.COLUMN_Name, [system.data.oledb.oledbtype]$_.DATA_TYPE Write-Host $msg -fore DarkCyan } } } $conn.Close() } Catch { Throw $_ } } Get-DataBaseSchema -AccessFile D:\test7\Database1.mdb -Verbose

    Clean and simple and easy to understand.

    Note that the "Jet" drivers are no longer supported by MS and can only be found in very old and unsupported versions of Office.  You can download the ACE drivers to any system even without Office.

    If you do the tutorial you will be able to understand what the New-Line function does and understand why it was used.  It was basically unnecessary and just made some of the output look colorful.

    Using Try/Catch eliminates the need for a path test.  The path error will be reported automatically by the "Open" method.

    Part of the reason you need to do a tutorial is to learn the structure of the "PowerShell Scripting System" and to learn the basics of OOP (Object Oriented Programming).  If you knew this you would not be asking the question you are asking.

    I recommend ignoring the script you found because it is an extremely bad example of how to write PS code.  It works but good programming techniques in PS would make it more like the code I posted above.

    Also the following will help you to understand other PowerShell coding standards.

    PowerShell Style Guidelines

    Also your question would be better asked to the authors of the script.  It is something only they can tell you their reason for doing.  TO me it seems like a mistake.


    • Edited by jrv Monday, January 14, 2019 9:10 AM
    Monday, January 14, 2019 9:06 AM
  • Many thansk, much appreciated!


    Monday, January 14, 2019 9:28 AM