PS C:\\> [enum]::Parse(\"system.data.oledb.oledbtype\",3)\nInteger\n<\/pre>\nAs 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:<\/p>\n
Function Get-DataType ($enum)\n{\n [enum]::Parse(\"system.data.oledb.oledbtype\",$enum)\n} #end Get-DataType\n<\/pre>\nNow we come to the main function we will be creating today. The Get-DataBaseSchema<\/b> function accepts a path to the database file as seen here:<\/p>\nFunction Get-DataBaseSchema ($Db)\n<\/pre>\nWe then call the New-Line<\/b> function to print out the name of the database with an underline:<\/p>\nNew-Line(\"Obtaining Schema for $Db\")\n<\/pre>\nNow we specify the provider and the data source, create the connection to the database, and open the connection. This is seen here:<\/p>\n
$Provider = \"Provider=Microsoft.Jet.OLEDB.4.0\"\n $DataSource = \"Data Source =\"+$Db\n $Connection = New-Object Data.OleDb.OleDbConnection(\"$Provider;$DataSource\")\n $Connection.open()\n<\/pre>\nWe retrieve the database schema by using the GetSchema<\/b> method. We are interested in tables, and therefore limit the returned information to tables by supplying the “Tables” keyword to the method<\/b> call. We then take the collection of table schema objects and pipeline it to the ForEach-Object<\/b> cmdlet, which allows us to work through the collection of objects. We use the $_<\/b> variable to refer to a single object on the pipeline. We look at the table_type<\/b> property. If it is equal to TABLE<\/b>, we know it was a user-created table and not a system table. We then sent the table name back to the $Tables<\/b> variable. This section of code is seen here:<\/p>\n$Tables = $Connection.GetSchema(\"Tables\") |\n ForEach-Object {\n if($_.Table_Type -eq \"TABLE\")\n { $_.Table_Name }\n }\n<\/pre>\nWell, 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<\/b> statement to walk through the array of table names stored in the $Tables<\/b> 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<\/b> function. This is seen here:<\/p>\nForEach($table in $Tables)\n {\n New-Line(\"Table Name: \" + $table)\n<\/pre>\nThe next thing we need to do is call the GetSchema<\/b> method and tell it we are interested in the columns. We then pipeline the columns to the ForEach-Object<\/b> cmdlet and look for columns that have the same table name that is stored in the $table<\/b> variable. After we have retrieved the columns for the particular table, we print out the column name and send the data_type<\/b> value to the Get-DataType<\/b> function. We talked about this earlier. This portion of the function is seen here: <\/p>\n $SchemaColumn = $Connection.GetSchema(\"Columns\")\n $schemaColumn |\n foreach-Object {\n If($_.Table_Name -eq $Table)\n { \"$($_.COLUMN_Name) $(Get-DataType($_.DATA_TYPE))\" }\n } #end Foreach-Object\n } #end Foreach table\n<\/pre>\nThe 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<\/b> method from the connection<\/b> object as seen here:<\/p>\n$Connection.Close()\n<\/pre>\nThe entry point to the script takes the path to the database, and then calls the Check-Path<\/b> and Get-DataBaseSchema<\/b> functions as seen here:<\/p>\n$Db = \"C:\\FSO\\ComputerData.mdb\"\nCheck-Path($Db)\nGet-DataBaseSchema($Db)\n<\/pre>\nWhen we run the script, we are given the output seen here:<\/p>\n
![\"Image](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/2009\/february\/hey0218\/hsg_2_18_09-01.jpg\")
<\/p>\n
<\/p>\n
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 \u201cHey, Scripting Guy!\u201d as Office Access Database Week continues. Until then, keep on scripting (thanks CA)!<\/p>\n
<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,54,49,3,45],"class_list":["post-54363","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=54363"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54363\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=54363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/h2>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
<\/p>\n