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() } } } } |