com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘trace_xe_action_map’. RSS feed

使用 DatabaseMetaData.getTables(null, “%”, “%”, new String[] {“TABLE”}); 在 MSSQL 的環境下會多出2個系統的 table.

trace_xe_action_map and trace_xe_event_map it’s some system table in system database.

From MS docs:
This table is stored in the master database, in the sys schema.


I am trying to get the names of all tables in a database in using Java. This is the method I am using. However, on running it, it also lists other tables, which are not actually part of my database, or maybe some system tables that I am not interested in. How can I get only the tables which I have created?

/* connecting to database using supplied credentials, and printing out the SQL queries for inserting data to local db */
public void connectToAzure(String connectionString, String databaseName, String username, String password) throws ClassNotFoundException, SQLException
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

final Connection m_Connection = DriverManager.getConnection( connectionString+";DatabaseName="+databaseName, username, password );
final ArrayList<String> tables = getTableNames(m_Connection);
}

/* helper method to get table names */
public ArrayList<String> getTableNames(final Connection m_Connection)
{
final ArrayList<String> tables = new ArrayList<>();
try
{
DatabaseMetaData dbmd = m_Connection.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", types);
while (rs.next())
{
String tableName = rs.getString("TABLE_NAME");
tables.add( tableName );
}
}
catch (SQLException e)
{
e.printStackTrace();
}

return tables;
}

Output on running the above

table1, table2, table3, table4, table5, table6, trace_xe_action_map, trace_xe_event_map

Of these,

trace_xe_action_map, trace_xe_event_map

are not tables that I have created.

Answer

Java 

You might want to run a simple select query on the database like this:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES;

You can further filter out the table_names using where clauses like these:

SELECT 
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_CATALOG = ? AND TABLE_SCHEMA = ?;

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *