{"record":{"id":"97a4e7c1061a912b","repo":"pentaho/pentaho-kettle","slug":"unable-to-determine-if-indexes-exists-on-table-tablename-97a4e7","errorCode":null,"errorMessage":"Unable to determine if indexes exists on table [\" + tablename + \"]","messagePattern":"Unable to determine if indexes exists on table \\[\" \\+ tablename \\+ \"\\]","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"core/src/main/java/org/pentaho/di/core/database/MSSQLServerDatabaseMeta.java","lineNumber":429,"sourceCode":"          return false;\n        }\n      } finally {\n        if ( res != null ) {\n          database.closeQuery( res );\n        }\n      }\n\n      // See if all the fields are indexed...\n      boolean all = true;\n      for ( int i = 0; i < exists.length && all; i++ ) {\n        if ( !exists[i] ) {\n          all = false;\n        }\n      }\n\n      return all;\n    } catch ( Exception e ) {\n      throw new KettleDatabaseException( \"Unable to determine if indexes exists on table [\" + tablename + \"]\", e );\n    }\n  }\n\n  @Override\n  public String getSQLListOfSchemas() {\n    return \"select name from sys.schemas\";\n  }\n\n  @Override\n  public boolean supportsSchemas() {\n    return true;\n  }\n\n  /**\n   * Get the SQL to insert a new empty unknown record in a dimension.\n   *\n   * @param schemaTable\n   *          the schema-table name to insert into","sourceCodeStart":411,"sourceCodeEnd":447,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/MSSQLServerDatabaseMeta.java#L411-L447","documentation":"MSSQLServerDatabaseMeta.checkIndexExists() runs SQL against SQL Server system views to determine whether the indexes used by Kettle exist on the table. Any failure (SQL error, permissions, unsupported SQL Server variant) is wrapped in this KettleDatabaseException. It means Kettle could not verify index existence — not necessarily that the indexes are missing.","triggerScenarios":"Calling checkIndexExists against a table on SQL Server when the metadata/index-lookup query throws — the DB user cannot read sys.indexes/sys.objects, the table name is invalid or quoted incorrectly, or the SQL Server version/Sybase variant lacks the queried catalog views.","commonSituations":"Running Kettle DDL checks with a limited login lacking VIEW DEFINITION; pointing a 'MSSQL' database type at Azure SQL/Sybase where the catalog query differs; table names with special characters/brackets needing different quoting.","solutions":["Read the cause for the underlying SQLException","Grant the connecting user permission to read catalog views (VIEW DEFINITION / SELECT on sys.*)","Verify the database type matches the actual server (MSSQL native vs MS SQL Server (native) vs Sybase)","Run the index-existence check manually with the table name to see the failing statement"],"exampleFix":"// before\n-- limited login → catalog query fails\nGRANT SELECT ON sys.indexes TO kettle_user;\n// after\nGRANT VIEW DEFINITION TO kettle_user;\nGRANT SELECT ON sys.indexes TO kettle_user;","handlingStrategy":"try-catch","validationCode":"Database db = new Database( dbMeta );\ndb.connect();\nResultSet rs = db.openQuery( \"SELECT 1 FROM sys.indexes\" ); // probe catalog access\nif ( rs == null ) log.warn( \"no access to sys.indexes — index checks will fail\" );","typeGuard":null,"tryCatchPattern":"try { exists = meta.checkIndexExists( table, idx, monitor ); } catch ( KettleDatabaseException e ) { log.error( \"index check failed for \" + table + \": \" + e.getCause(), e ); }","preventionTips":["Grant VIEW DEFINITION to the Kettle service account","Use the correct SQL Server database type for the server variant","Quote/sanitize table names with special characters","Test DDL/catalog queries manually with the same login"],"tags":["database","sqlserver","metadata","index"],"backgroundTag":"sql-query-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}