Hey, Scripting Guy! Is there a way to figure out which tables and columns are in a database without having to open it up?

– DT

Hi DT,

The Impressionists are back in Chicago! For the past six months, they were hibernating down in Ft. Worth, Texas, in the United States. But now they are back. In true Ferris Bueller style, I am standing here looking at Cezanne’s The Vase of Tulips and am impressed by the way the flowers seem at ease with their surroundings, are at one with their vase, and enjoy a brief repose with the fruit on the table (of course the fruit is innocent—it was framed; we’ll be here all week, try the veal). It is the structure of the painting that creates an organic entity from myriad brush strokes. It is the schema that impresses me.

To become one with your database, you will also want to enjoy the schema of the database. While waiting for a pizza, we knocked out the GetDataBaseSchema.ps1 script.

The The GetDataBaseSchema.ps1 script is seen here.

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   {
     Throw "$(Split-Path -path $Db -parent) Does not Exist"
   }
  ELSE
  {
   If (!(Test-Path -Path $Db))
     {
      Throw "$db does not exist"
     }
  }
} #End Check-Path
Function New-Line (
                  $strIN,
                  $char = "=",
                  $sColor = "Yellow",
                  $uColor = "darkYellow",
                  [switch]$help
                 )
{
 if($help)
  {
    $local:helpText = `
@"
     New-Line accepts inputs: -strIN for input string and -char for seperator
     -sColor for the string color, and -uColor for the underline color. Only
     the -strIn is required. The others have the following default values:
     -char: =, -sColor: Yellow, -uColor: darkYellow
     Example:
     New-Line -strIN "Hello world"
     New-Line -strIn "Morgen welt" -char "-" -sColor "blue" -uColor "yellow"
     New-Line -help
"@
   $local:helpText
   break
  } #end New-Line help
 $strLine= $char * $strIn.length
 Write-Host -ForegroundColor $sColor $strIN
 Write-Host -ForegroundColor $uColor $strLine
} #end New-Line function
Function Get-DataType ($enum)
{
 [enum]::Parse("system.data.oledb.oledbtype",$enum)
} #end Get-DataType
Function Get-DataBaseSchema ($Db)
{
 New-Line("Obtaining Schema for $Db")
 $Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
 $DataSource = "Data Source ="+$Db
 $Connection = New-Object Data.OleDb.OleDbConnection("$Provider;$DataSource")
 $Connection.open()
 $Tables = $Connection.GetSchema("Tables") |
   ForEach-Object {
      if($_.Table_Type -eq "TABLE")
        { $_.Table_Name }
     }
   ForEach($table in $Tables)
    {
     New-Line("Table Name: " + $table)
     $SchemaColumn = $Connection.GetSchema("Columns")
     $schemaColumn |
     foreach-Object {
        If($_.Table_Name -eq $Table)
         { "$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE))" }
      } #end Foreach-Object
    } #end Foreach table
$Connection.Close()
}
# *** Entry Point to Script ***
$Db = "C:\FSO\ComputerData.mdb"
Check-Path($Db)
Get-DataBaseSchema($Db)

Ok, so the GetDataBaseShema.ps1 script is rather long. But we have already talked about several of the functions recently:

The Get-DataType function accepts a single parameter—a number that represents a data type enumeration value. Inside the function, it uses the Parse static method from the System.Enum .NET Framework class. The Parse method takes two parameters: the first is the name of the enumeration class, and the second is the value of the enumeration. If we were to call this from within the Windows PowerShell console, it might look like this:

PS C:\> [enum]::Parse("system.data.oledb.oledbtype",3)
Integer

As you can see, it takes a number and turns it into a string, so the output is more intelligible. We will use this when we display the data types contained in each column of the database table. The function is seen here:

Function Get-DataType ($enum)
{
 [enum]::Parse("system.data.oledb.oledbtype",$enum)
} #end Get-DataType

Now we come to the main function we will be creating today. The Get-DataBaseSchema function accepts a path to the database file as seen here:

Function Get-DataBaseSchema ($Db)

We then call the New-Line function to print out the name of the database with an underline:

New-Line("Obtaining Schema for $Db")

Now we specify the provider and the data source, create the connection to the database, and open the connection. This is seen here:

$Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
 $DataSource = "Data Source ="+$Db
 $Connection = New-Object Data.OleDb.OleDbConnection("$Provider;$DataSource")
 $Connection.open()

We retrieve the database schema by using the GetSchema method. We are interested in tables, and therefore limit the returned information to tables by supplying the “Tables” keyword to the method call. We then take the collection of table schema objects and pipeline it to the ForEach-Object cmdlet, which allows us to work through the collection of objects. We use the $_ variable to refer to a single object on the pipeline. We look at the table_type property. If it is equal to TABLE, we know it was a user-created table and not a system table. We then sent the table name back to the $Tables variable. This section of code is seen here:

$Tables = $Connection.GetSchema("Tables") |
   ForEach-Object {
      if($_.Table_Type -eq "TABLE")
        { $_.Table_Name }
     }

Well, that was as much fun as extra mushrooms and sausage on a Chicago deep-dish pizza. Because it is possible the database could have more than user-created table in the database, we use the ForEach statement to walk through the array of table names stored in the $Tables variable. If there is only one table, we do not need to do anything because Windows PowerShell does not generate an error the way VBScript would. We then print out the name of the table and underline it by using the New-Line function. This is seen here:

ForEach($table in $Tables)
    {
     New-Line("Table Name: " + $table)

The next thing we need to do is call the GetSchema method and tell it we are interested in the columns. We then pipeline the columns to the ForEach-Object cmdlet and look for columns that have the same table name that is stored in the $table variable. After we have retrieved the columns for the particular table, we print out the column name and send the data_type value to the Get-DataType function. We talked about this earlier. This portion of the function is seen here:

     $SchemaColumn = $Connection.GetSchema("Columns")
     $schemaColumn |
     foreach-Object {
        If($_.Table_Name -eq $Table)
         { "$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE))" }
      } #end Foreach-Object
    } #end Foreach table

The last thing we need to do is close the connection to the database. This is important to free resources and to minimize database locking issues. To close the database, we use the Close method from the connection object as seen here:

$Connection.Close()

The entry point to the script takes the path to the database, and then calls the Check-Path and Get-DataBaseSchema functions as seen here:

$Db = "C:\FSO\ComputerData.mdb"
Check-Path($Db)
Get-DataBaseSchema($Db)

When we run the script, we are given the output seen here:

Well, DT, we have come to the conclusion of another script. I predict you will be able to use the ability to write to an Access Database often. We will see you tomorrow for another really cool installment of “Hey, Scripting Guy!” as Office Access Database Week continues. Until then, keep on scripting (thanks CA)!

Ed Wilson and Craig Liebendorfer, Scripting Guys