Public/Get-OleDbIndexMetaData.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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
#Require -Version 5.0

function Get-OleDbIndexMetadata {
    <#
    .SYNOPSIS
    Retrieve index metadata from any OleDb source, given a driver and a location
 
    .DESCRIPTION
    Retrieve index metadata from any OleDb source, given a driver and a location
 
    .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 IndexName
    What is the name of the index(es) of interest? Null means 'all indexes'.
 
    .PARAMETER Type
    What is the type of indexes of interest? Null means 'all types'.
 
    .PARAMETER TableName
    What is the name of the table(s) of interest? Null means 'all tables'.
 
    .EXAMPLE
    Get-OleDbIndexMetadata -DataSource '.\SQL2016' -Provider 'sqloledb' -ExtendedProperties "Trusted_Connection=Yes"
 
    .EXAMPLE
    Get-OleDbIndexMetadata -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,

        $TableCatalog,
        $TableSchema,
        $IndexName,
        $Type,
        $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]::Indexes, ($TableCatalog, $TableSchema, $TableName, $Type))).ForEach({
                [PSCustomObject] @{
                    TableCatalog    = $_.TABLE_CATALOG
                    TableSchema     = $_.TABLE_SCHEMA
                    TableName       = $_.TABLE_NAME
                    IndexCatalog    = $_.INDEX_CATALOG
                    IndexSchema     = $_.INDEX_SCHEMA
                    IndexName       = $_.INDEX_NAME
                    PrimaryKey      = $_.PRIMARY_KEY
                    Unique          = $_.UNIQUE
                    Clustered       = $_.CLUSTERED
                    Type            = $_.TYPE
                    FillFactor      = $_.FILL_FACTOR
                    InitialSize     = $_.INITIAL_SIZE
                    Nulls           = $_.NULLS
                    SortBookmarks   = $_.SORT_BOOKMARKS
                    AutoUpdate      = $_.AUTO_UPDATE
                    NullCollation   = $_.NULL_COLLATION
                    OrdinalPosition = $_.ORDINAL_POSITION
                    ColumnName      = $_.COLUMN_NAME
                    ColumnGuid      = $_.COLUMN_GUID
                    ColumnPropID    = $_.COLUMN_PROPID
                    Collation       = $_.COLLATION
                    Cardinality     = $_.CARDINALITY
                    Pages           = $_.PAGES
                    FilterCondition = $_.FILTER_CONDITION
                    Integrated      = $_.INTEGRATED
                    # 'Expression' is in the ADO version of this tool, but not in the OleDb version.
                    # Expression = $_.EXPRESSION
                    Datasource      = $Datasource
                }
            }
        )


        # $OleDbConn.GetOleDbSchemaTable([OleDbSchemaGuid]::Indexes, ($TableCatalog, $TableSchema, $IndexName, $Type, $TableName)) |
        # Select-Object @{n = "TableCatalog"; e = { $_.TABLE_CATALOG } },
        # @{n = "TableSchema"; e = { $_.TABLE_SCHEMA } },
        # @{n = "TableName"; e = { $_.TABLE_NAME } },

        # @{n = "IndexCatalog"; e = { $_.INDEX_CATALOG } },
        # @{n = "IndexSchema"; e = { $_.INDEX_SCHEMA } },
        # @{n = "IndexName"; e = { $_.INDEX_NAME } },

        # @{n = "PrimaryKey"; e = { $_.PRIMARY_KEY } },
        # @{n = "Unique"; e = { $_.UNIQUE } },
        # @{n = "Clustered"; e = { $_.CLUSTERED } },
        # @{n = "Type"; e = { $_.TYPE } },

        # @{n = "FillFactor"; e = { $_.FILL_FACTOR } },
        # @{n = "InitialSize"; e = { $_.INITIAL_SIZE } },
        # @{n = "Nulls"; e = { $_.NULLS } },

        # @{n = "SortBookmarks"; e = { $_.SORT_BOOKMARKS } },
        # @{n = "AutoUpdate"; e = { $_.AUTO_UPDATE } },
        # @{n = "NullCollation"; e = { $_.NULL_COLLATION } },
        # @{n = "OrdinalPosition"; e = { $_.ORDINAL_POSITION } },
        # @{n = "ColumnName"; e = { $_.COLUMN_NAME } },
        # @{n = "ColumnGuid"; e = { $_.COLUMN_GUID } },
        # @{n = "ColumnPropID"; e = { $_.COLUMN_PROPID } },
        # @{n = "Collation"; e = { $_.COLLATION } },
        # @{n = "Cardinality"; e = { $_.CARDINALITY } },
        # @{n = "Pages"; e = { $_.PAGES } },
        # @{n = "FilterCondition"; e = { $_.FILTER_CONDITION } },
        # @{n = "Integrated"; e = { $_.INTEGRATED } },
        # # 'Expression' is in the ADO version of this tool, but not in the OleDb version.
        # # @{n = "Expression"; e = {$_.EXPRESSION}}
        # @{n = "Datasource"; e = { $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()
            }
        }
    }
}