文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Powershell ODBC获取架构元数据

问Powershell ODBC获取架构元数据
EN

Stack Overflow用户
提问于 2022-01-11 00:14:31
回答 1查看 741关注 0票数 0

是否有人知道是否有方法使用Powershell获取ODBC连接的架构数据？我正在使用一个旧的数据库管理系统，没有什么比"Information_Schema“更好了。我希望能够获得一个表的列表及其所有相关的列、数据类型等。

database
powershell
odbc
EN

回答 1

Stack Overflow用户

发布于 2022-01-11 16:23:09

您可能需要调整连接字符串和对象结构以适应特定的DSN，但这是它的要点。

代码语言：javascript
复制
function Get-ODBCSchema{
   param(
      [string]
      [ValidateNotNullOrEmpty()]
      $DataSourceName
   )
   try{$dsn = get-odbcdsn $DataSourceName -ErrorAction Stop}catch {
      throw $_.Exception
   }
   if($dsn.Platform -NotMatch ([IntPtr]::Size*8))
      {throw "You're currently in $([IntPtr]::Size*8)-Bit PowerShell, and the ODBC is only available in $($dsn.Platform)"}
   
   $con = [System.Data.Odbc.OdbcConnection]::new()
   $con.ConnectionString = 'DSN={0}' -f $DataSourceName
   $con.open()
   write-host "Downloading Column Data from $DataSourceName, this may take some time"
   $columns = $con.GetSchema('Columns')
   write-host 'got it'
   $con.close();$con.dispose()
   $count = $columns.Rows.Count
   [int]$aPercent = $count / 100
   
   $ret = @{}; $i = $j = 0
   $columns.Rows.GetEnumerator().foreach{
      $qual  = $_.TABLE_QUALIFIER.ToString()
      $owner = $_.TABLE_OWNER.ToString()
      $tName = $_.TABLE_NAME.ToString()
      $cName = $_.COLUMN_NAME.ToString()
      
      if($ret.Keys -NotContains $qual)
         {$ret.$qual = @{$owner = @{$tName = @{$cName = $_}}}}
      elseif($ret.$qual.Keys -NotContains $owner)
         {$ret.$qual.$owner = @{$tName = @{$cName = $_}}}
      elseif($ret.$qual.$owner.Keys -NotContains $tName)
         {$ret.$qual.$owner.$tName = @{$cName = $_}}
      else
         {$ret.$qual.$owner.$tName.$cName = $_}
      if(!($i++ % $aPercent)){
         write-progress -Activity 'Building Schema Table' -Status ('Added Column: {0:#,##0} of {1:#,##0}' -f $i,$count) -PercentComplete ($j++)
      }
   }
   write-progress -Activity 'Building Schema Table' -Completed
   if($ret.Keys.Count -eq 1 -And $ret.Keys[0].Length -eq 0)
      {$ret = $ret.Values[0]}
   return $ret
}

Edit1:找出需要微调的内容

打开PowerShell

代码语言：javascript
复制
PS C:\working> 'This PowerShell session is running in {0}-bit mode' -f ([IntPtr]::Size*8)
This PowerShell session is running in 64-bit mode
PS C:\working> Get-OdbcDsn 'Excel Files'
Name       : Excel Files
DsnType    : User
Platform   : 32-bit
DriverName : Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Attribute  : {SafeTransactions, DriverId, ImplicitCommitSync, Threads...}

Dang，DSN平台只在32位模式下工作，但是这个PS是64位的.所以我想我们需要启动一个新的32位会话

代码语言：javascript
复制
PS C:\working> start-process $env:SystemRoot\syswow64\windowspowershell\v1.0\powershell.exe

好吧，现在我们应该开始挖掘了

代码语言：javascript
复制
PS C:\working> 'This PowerShell session is running in {0}-bit mode' -f ([IntPtr]::Size*8)
This PowerShell session is running in 32-bit mode
PS C:\working> $con = [System.Data.Odbc.OdbcConnection]::new()
PS C:\working> $con.ConnectionString = 'DSN=Excel Files'
PS C:\working> $con.Open()
Exception calling "Open" with "0" argument(s): "ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed"
At line:1 char:1
+ $con.Open()
+ ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OdbcException

好吧，也许需要一个文件名？

代码语言：javascript
复制
PS C:\working> $con.ConnectionString = 'DSN=Excel Files;File Name={0}' -f (ls '.\Book1.xlsx').FullName
PS C:\working> $con.Open()
Exception calling "Open" with "0" argument(s): "ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute File Name"
At line:1 char:1
+ $con.Open()
+ ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OdbcException

不同的错误，希望朝着正确的方向前进，现在它在抱怨attribute File Name

代码语言：javascript
复制
PS C:\working> $con.ConnectionString = 'DSN=Excel Files;Data Source={0}' -f (ls '.\Book1.xlsx').FullName
PS C:\working> $con.Open()
Exception calling "Open" with "0" argument(s): "ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute Data Source"
At line:1 char:1
+ $con.Open()
+ ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OdbcException

愚蠢的运气到此为止；显然，这也不是attribute Data Source。也许我该去找出什么Excel的神奇词汇是？看起来可能是Dbq；因为.擅长吗？

代码语言：javascript
复制
PS C:\working> $con.ConnectionString = 'DSN=Excel Files;Dbq={0}' -f (ls '.\Book1.xlsx').FullName
PS C:\working> $con.Open()
PS C:\working>

就像这样，我们已经踏足了大门，让我们看看能找到什么。

代码语言：javascript
复制
PS C:\working> $con.GetSchema()

CollectionName        NumberOfRestrictions NumberOfIdentifierParts
--------------        -------------------- -----------------------
MetaDataCollections                      0                       0
DataSourceInformation                    0                       0
DataTypes                                0                       0
Restrictions                             0                       0
ReservedWords                            0                       0
Columns                                  4                       4
Indexes                                  4                       4
Tables                                   3                       3
Views                                    3                       3


PS C:\working> $tableinfo = $con.GetSchema('Tables')
PS C:\working> $enu = $tableinfo.Rows.GetEnumerator()
PS C:\working> $enu.MoveNext()
True
PS C:\working> $enu.Current


TABLE_CAT   : C:\working\Book1.xlsx
TABLE_SCHEM :
TABLE_NAME  : Sheet1$
TABLE_TYPE  : SYSTEM TABLE
REMARKS     :



PS C:\working> $columninfo = $con.GetSchema('Columns')
PS C:\working> $enu = $columninfo.Rows.GetEnumerator()
PS C:\working> $enu.MoveNext();$enu.Current
True


TABLE_CAT         : C:\working\Book1.xlsx
TABLE_SCHEM       :
TABLE_NAME        : Sheet1$
COLUMN_NAME       : Payer No
DATA_TYPE         : 8
TYPE_NAME         : NUMBER
COLUMN_SIZE       : 53
BUFFER_LENGTH     : 8
DECIMAL_DIGITS    :
NUM_PREC_RADIX    : 2
NULLABLE          : 1
REMARKS           :
COLUMN_DEF        :
SQL_DATA_TYPE     : 8
SQL_DATETIME_SUB  :
CHAR_OCTET_LENGTH :
ORDINAL_POSITION  : 1
IS_NULLABLE       : YES
ORDINAL           : 1



PS C:\working> $con.Close();$con.Dispose();remove-variable con,enu;[gc]::Collect()
PS C:\working>

嗯，看来我需要对Get-ODBCSchema做一些调整，以便它可以使用这个数据源；即：TABLE_QUALIFIER到TABLE_CAT，TABLE_OWNER到TABLE_SCHEM，并添加对文件名的支持。

代码语言：javascript
复制
PS C:\working> function Get-ODBCSchema{
>>    param(
>>       [string]
>>       [ValidateNotNullOrEmpty()]
>>       $DataSourceName
>>       ,[string][ValidateNotNullOrEmpty()]$FullFileName
>>    )
>>    if(!(test-path $FullFileName -type leaf))                                                                         >>       {throw "Missing file: $FullFileName"}                                                                          >>    try{$dsn = get-odbcdsn $DataSourceName -ErrorAction Stop}catch {                                                  >>       throw $_.Exception                                                                                             >>    }                                                                                                                 >>    if($dsn.Platform -NotMatch ([IntPtr]::Size*8))
>>       {throw "You're currently in $([IntPtr]::Size*8)-Bit PowerShell, and the ODBC is only available in $($dsn.Platform)"}
>>
>>    $con = [System.Data.Odbc.OdbcConnection]::new()
>>    $con.ConnectionString = 'DSN={0};Dbq={1}' -f $DataSourceName,$FullFileName
>>    $con.open()
>>    write-host "Downloading Column Data from $DataSourceName, this may take some time"
>>    $columns = $con.GetSchema('Columns')
>>    write-host 'got it'
>>    $con.close();$con.dispose()
>>    $count = $columns.Rows.Count
>>    [int]$aPercent = $count / 100
>>
>>    $ret = @{}; $i = $j = 0
>>    $columns.Rows.GetEnumerator().foreach{
>>       $qual  = $_.TABLE_CAT.ToString()
>>       $owner = $_.TABLE_SCHEM.ToString()
>>       $tName = $_.TABLE_NAME.ToString()
>>       $cName = $_.COLUMN_NAME.ToString()
>>
>>       if($ret.Keys -NotContains $qual)
>>          {$ret.$qual = @{$owner = @{$tName = @{$cName = $_}}}}
>>       elseif($ret.$qual.Keys -NotContains $owner)
>>          {$ret.$qual.$owner = @{$tName = @{$cName = $_}}}
>>       elseif($ret.$qual.$owner.Keys -NotContains $tName)
>>          {$ret.$qual.$owner.$tName = @{$cName = $_}}
>>       else
>>          {$ret.$qual.$owner.$tName.$cName = $_}
>>       if(!($i++ % $aPercent)){
>>          write-progress -Activity 'Building Schema Table' -Status ('Added Column: {0:#,##0} of {1:#,##0}' -f $i,$count) -PercentComplete ($j++)
>>       }
>>    }
>>    write-progress -Activity 'Building Schema Table' -Completed
>>    if($ret.Keys.Count -eq 1 -And $ret.Keys[0].Length -eq 0)
>>       {$ret = $ret.Values[0]}
>>    return $ret
>> }
PS C:\working> $s = Get-ODBCSchema 'Excel Files' (ls .\Book1.xlsx).FullName
Downloading Column Data from Excel Files, this may take some time
got it
PS C:\working> $s

Name                           Value
----                           -----
C:\working\Book1.xlsx          {}


PS C:\working> $s.Values

Name                           Value
----                           -----
                               {Sheet3$, Sheet1$, Sheet2$, Sheet2$_xlnm#_FilterDatabase...}


PS C:\working> $s.Keys
C:\working\Book1.xlsx
PS C:\working> [string]$k1 = $s.keys[0]
PS C:\working> $s.$k1

Name                           Value
----                           -----
                               {Sheet3$, Sheet1$, Sheet2$, Sheet2$_xlnm#_FilterDatabase...}


PS C:\working> $s.$k1.values

Name                           Value
----                           -----
Sheet3$                        {F1}
Sheet1$                        {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}
Sheet2$                        {FileName, EqLoc City, Invoice No, Sub...}
Sheet2$_xlnm#_FilterDatabase   {FileName, EqLoc City, Invoice No, Sub...}
Sheet1$_xlnm#_FilterDatabase   {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}


PS C:\working> $s.$k1.values[0]

Name                           Value
----                           -----
Sheet3$                        {F1}
Sheet1$                        {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}
Sheet2$                        {FileName, EqLoc City, Invoice No, Sub...}
Sheet2$_xlnm#_FilterDatabase   {FileName, EqLoc City, Invoice No, Sub...}
Sheet1$_xlnm#_FilterDatabase   {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}


PS C:\working> $s.$k1 = $s.$k1.values[0]
PS C:\working> $s

Name                           Value
----                           -----
C:\working\Book1.xlsx          {System.Collections.Hashtable}


PS C:\working> $s.$k1

Name                           Value
----                           -----
Sheet3$                        {F1}
Sheet1$                        {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}
Sheet2$                        {FileName, EqLoc City, Invoice No, Sub...}
Sheet2$_xlnm#_FilterDatabase   {FileName, EqLoc City, Invoice No, Sub...}
Sheet1$_xlnm#_FilterDatabase   {Due Date, Invoice No, REC_STAT2, Comp Invoice Date...}


PS C:\working> $s.$k1.'Sheet1$'

Name                           Value
----                           -----
Due Date                       System.Data.DataRow
Invoice No                     System.Data.DataRow
REC_STAT2                      System.Data.DataRow
Comp Invoice Date              System.Data.DataRow
Equip Loc Street 1             System.Data.DataRow
T...

所以，我想这或多或少是可行的，唯一剩下的就是清理掉在头部折叠空分支的函数末尾的位，然后将第一个子部分折叠起来。

代码语言：javascript
复制
function Get-ODBCSchema{
   param(
      [string]
      [ValidateNotNullOrEmpty()]
      $DataSourceName
      ,[string][ValidateNotNullOrEmpty()]$FullFileName
   )
   if(!(test-path $FullFileName -type leaf))
      {throw "Missing file: $FullFileName"}
   try{$dsn = get-odbcdsn $DataSourceName -ErrorAction Stop}catch {
      throw $_.Exception
   }
   if($dsn.Platform -NotMatch ([IntPtr]::Size*8))
      {throw "You're currently in $([IntPtr]::Size*8)-Bit PowerShell, and the ODBC is only available in $($dsn.Platform)"}

   $con = [System.Data.Odbc.OdbcConnection]::new()
   $con.ConnectionString = 'DSN={0};Dbq={1}' -f $DataSourceName,$FullFileName
   $con.open()
   write-host "Downloading Column Data from $DataSourceName, this may take some time"
   $columns = $con.GetSchema('Columns')
   write-host 'got it'
   $con.close();$con.dispose()
   $count = $columns.Rows.Count
   [int]$aPercent = $count / 100

   $ret = @{}; $i = $j = 0
   $columns.Rows.GetEnumerator().foreach{
      $qual  = $_.TABLE_CAT.ToString()
      $owner = $_.TABLE_SCHEM.ToString()
      $tName = $_.TABLE_NAME.ToString()
      $cName = $_.COLUMN_NAME.ToString()

      if($ret.Keys -NotContains $qual)
         {$ret.$qual = @{$owner = @{$tName = @{$cName = $_}}}}
      elseif($ret.$qual.Keys -NotContains $owner)
         {$ret.$qual.$owner = @{$tName = @{$cName = $_}}}
      elseif($ret.$qual.$owner.Keys -NotContains $tName)
         {$ret.$qual.$owner.$tName = @{$cName = $_}}
      else
         {$ret.$qual.$owner.$tName.$cName = $_}
      if(!($i++ % $aPercent)){
         write-progress -Activity 'Building Schema Table' -Status ('Added Column: {0:#,##0} of {1:#,##0}' -f $i,$count) -PercentComplete ($j++)
      }
   }
   write-progress -Activity 'Building Schema Table' -Completed
   [string]$k1 = $ret.Keys[0]
   if($ret.$k1.Keys.Count -eq 1 -And $ret.$k1.Keys[0].Length -eq 0)
      {$ret.$k1 = $ret.$k1.Values[0]}
   return $ret
}

现在我应该有一个适用于Excel Files dsn的版本。

代码语言：javascript
复制
$schema = Get-ODBCSchema 'Excel Files' (ls .\Book1.xlsx).FullName

Edit2:用它做点什么

既然我们在导出模式时遇到了很多麻烦，那么不妨用它制作一些表.

代码语言：javascript
复制
PS C:\working> $WorkBook = ls .\Book1.xlsx
PS C:\working> $schema = Get-ODBCSchema 'Excel Files' $WorkBook.FullName
Downloading Column Data from Excel Files, this may take some time
got it
PS C:\working> function Get-TypeString{
>>    param(
>>        [Parameter(ValueFromPipelineByPropertyName=$true)]$COLUMN_NAME
>>       ,[Parameter(ValueFromPipelineByPropertyName=$true)]$TYPE_NAME
>>       ,$TabWidth = 30
>>    )process{
>>    $out = switch($TYPE_NAME){
>>     'VARCHAR' {"   ,{0,-$TabWidth} varchar(255)";break}
>>     'NUMBER'  {"   ,{0,-$TabWidth} decimal(18,5)";break}
>>     'DATETIME'{"   ,{0,-$TabWidth} datetime";break}
>>     default   {"   ,{0,-$TabWidth} varchar(255)";break}
>>    }
>>    $out -f "[$COLUMN_NAME]"
>> }}
PS C:\working> $DBSchema = 'GreenField.dbo.'
PS C:\working> $sql = [System.Text.StringBuilder]::new(@"
>> /*
>> * Infrastructure as Code
>> * SQL Server - Data Description Language
>> * Auto Generated From:
>> *    $($WorkBook.FullName)
>> */
>> "@)
PS C:\working> [string]$wb = $schema.Keys[0]
PS C:\working> [string[]]$tables = $schema.$wb.Keys | sort-object
PS C:\working> $tables.foreach{
>>    $table  = $_
>>    if($schema.$wb.$table.Keys.Count -eq 0)
>>       {continue}
>>    $fields = $schema.$wb.$table.GetEnumerator() | sort-object -Property @{Expression={$_.Value.ORDINAL};Ascending=$true}
>>    if($fields.Count -eq 1)
>>       {$fields = @($fields)}
>>    $enu = $fields.GetEnumerator()
>>    $null = $enu.MoveNext()
>>    $null = $sql.Append("`r`n`r`n")
>>    $null = $sql.Append(((@'
>>       if OBJECT_ID('{0}[{1}]') is not null
>>          drop table {0}[{1}];
>>       create table {0}[{1}](
>> '@ -f $DBSchema,$table) -replace '(^|\n)\s{6}','$1'))
>>    $null = $sql.Append("`r`n")
>>    $null = $sql.Append((($enu.Current.Value|Get-TypeString) -replace '^(\s+),','$1 '))
>>    $enu.foreach{
>>       $null = $sql.Append("`r`n")
>>       $null = $sql.Append(($_.Value|Get-TypeString))
>>    }
>>    $null = $sql.Append("`r`n);")
>> }
PS C:\working> $SQLFile = '{0}-IaC-DDL.sqlserver.sql' -f $WorkBook.Name.Replace($WorkBook.Extension,'')
PS C:\working> new-item $SQLFile -Value $sql.ToString() -Force


    Directory: C:\working


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----         1/13/2022   5:32 PM          14908 Book1-IaC-DDL.sqlserver.sql


PS C:\working> gc .\Book1-IaC-DDL.sqlserver.sql
/*
* Infrastructure as Code
* SQL Server - Data Description Language
* Auto Generated From:
*    C:\working\Book1.xlsx
*/

if OBJECT_ID('GreenField.dbo.[Sheet1$]') is not null
   drop table GreenField.dbo.[Sheet1$];
create table GreenField.dbo.[Sheet1$](
    [Payer No]                     decimal(18,5)
   ,[Composite Invoice]            decimal(18,5)
   ,[Comp Invoice Date]            datetime
...

if OBJECT_ID('GreenField.dbo.[Sheet3$]') is not null
   drop table GreenField.dbo.[Sheet3$];
create table GreenField.dbo.[Sheet3$](
    [F1]                           varchar(255)
);
PS C:\working>
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/70660296

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档