Public/Get-OleDbColumnMetaData.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
#Require -Version 5.0
using namespace System.Data.OleDb

function Get-OleDbColumnMetadata {
    <#
    .SYNOPSIS
    Shows column metadata for OleDb databases
 
    .DESCRIPTION
    Shows column metadata for OleDb databases
 
    .PARAMETER DataSource
    Which location is of interest? This is uusally some sort of server hostname or a file path.
 
    .PARAMETER Credential
    Use alternative credentials. Accepts credential objects provided by Get-Credential.
 
    .PARAMETER Connection
    If the caller provides a "live", open connection, it will be used. The connection will not be closed.
 
    .PARAMETER ConnectionString
    If the caller provides a connection string, use that.
 
    .PARAMETER Provider
    Which OleDB provider should be used?
 
    .PARAMETER ExtendedProperties
    What extended property values should be used by the OleDB provider?
 
    .PARAMETER TableCatalog
    What is the name of the table(s) of interest? Null means 'the default catalog'.
    For databases like SQL Server 'TableCatalog' means 'database name'.
 
    .PARAMETER TableSchema
    What is the schema name of the table(s) of interest? Null means 'all schemas'.
 
    .PARAMETER TableName
    What is the name of the table(s) of interest? Null means 'all tables'.
 
    .EXAMPLE
    Get-OleDbColumnMetadata -DataSource '.\SQL2016' -Provider 'sqloledb' -ExtendedProperties "Trusted_Connection=Yes"
 
    .EXAMPLE
    Get-OleDbColumnMetadata -DataSource '.\SQL2016' -Provider 'sqloledb' -ExtendedProperties "Trusted_Connection=Yes" -TableCatalog 'AdventureWorks2016' -TableSchema 'Sales' -TableType 'TABLE'
 
    .LINK
    https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection.getoledbschematable?view=netframework-4.7.2
    #>


    param (
        [Parameter(
            ParameterSetName = 'WithConnection',
            Mandatory = $true
        )]
        [System.Data.OleDb.OleDbConnection] $Connection,

        [Parameter(
            ParameterSetName = 'WithConnectionString',
            Mandatory = $true
        )]
        [string] $ConnectionString,

        [Parameter(
            ParameterSetName = 'WithDataSource',
            Mandatory = $true
        )]
        [string] $DataSource,

        [Parameter(
            ParameterSetName = 'WithDataSource',
            Mandatory = $true
        )]
        [string] $Provider,

        [Parameter(
            ParameterSetName = 'WithDataSource'
        )]
        [string] $ExtendedProperties,

        [Parameter(
            ParameterSetName = 'WithDataSource'
        )]
        [System.Management.Automation.PSCredential] $Credential,

        [string] $TableCatalog,
        [string] $TableSchema,
        [string] $TableName
    )

    Try {
        switch ($PSCmdlet.ParameterSetName) {
            'WithConnection' {
                $OleDbConn = $Connection
            }
            'WithConnectionString' {
                $OleDbConn = Get-OleDbConnection -ConnectionString $ConnectionString
            }
            'WithDataSource' {
                $OleDbConn = Get-OleDbConnection -DataSource $DataSource -Provider $Provider -ExtendedProperties $ExtendedProperties -Credential $Credential
            }
        }

        # Doc for parameters for GetOleDbSchemaTable call:
        # https://social.msdn.microsoft.com/Forums/en-US/75fb3085-bc3d-427c-9257-30631235c3af/getoledbschematableoledboledbschemaguidindexes-how-to-access-included-columns-on-index?forum=vblanguage
        # because of the way that this call works, the four parameters here can't be declared as [string] in PowerShell.
        # It seems to have to do with the nullability of the variables. There seems to be a difference between:
        # [string], [nullable][string] and <no datatype declaration>.

        ($OleDbConn.GetOleDbSchemaTable([OleDbSchemaGuid]::Columns, ($TableCatalog, $TableSchema, $TableName, $Type))).ForEach({
                [PSCustomObject] @{
                    TableCatalog           = $_.TABLE_CATALOG
                    TableSchema            = $_.TABLE_SCHEMA
                    TableName              = $_.TABLE_NAME

                    ColumnName             = $_.COLUMN_NAME
                    OrdinalPosition        = $_.ORDINAL_POSITION
                    ColumnHasDefault       = $_.COLUMN_HASDEFAULT
                    ColumnDefault          = $_.COLUMN_DEFAULT
                    # .TODO
                    # is this a bitwise column?
                    # Can you translate this, either at this, the ADO layer, or at the caller layer?
                    ColumnFlags            = $_.COLUMN_FLAGS
                    IsNullable             = $_.IS_NULLABLE
                    DataType               = $_.DATA_TYPE
                    # I am adding this as a user convenience; looking at the Raw data_type IDs is not helpful
                    DataTypeDescription    = [OleDbType]($_.DATA_TYPE)
                    NumericPrecision       = $_.NUMERIC_PRECISION
                    NumericScale           = $_.NUMERIC_SCALE
                    CharacterMaximumLength = $_.CHARACTER_MAXIMUM_LENGTH
                    CharacterOctetLength   = $_.CHARACTER_OCTET_LENGTH
                    CharacterSetCatalog    = $_.CHARACTER_SET_CATALOG
                    CharacterSetSchema     = $_.CHARACTER_SET_SCHEMA
                    CharacterSetName       = $_.CHARACTER_SET_NAME
                    CollationCatalog       = $_.COLLATION_CATALOG
                    CollationSchema        = $_.COLLATION_SCHEMA
                    CollationName          = $_.COLLATION_NAME
                    DomainCatalog          = $_.DOMAIN_CATALOG
                    DomainSchema           = $_.DOMAIN_SCHEMA
                    DomainName             = $_.DOMAIN_NAME
                    IsComputed             = $_.IS_COMPUTED
                    Datasource             = $Datasource
                }
            }
        )
    }

    Catch {
        Throw
    }

    Finally {
        # if we were passed a connection, do not close it. Closing it is the responsibility of the caller.
        if ($PSCmdlet.ParameterSetName -ne 'WithConnection') {
            # Do not free connections that don't exist
            if ($OleDbConn) {
                $OleDbConn.Close()
                $OleDbConn.Dispose()
            }
        }
    }

}