getTables in DatabaseMetadata not giving proper results

[Amrit Tiwana]

Hi,

I'm using following code to get all tables types with name 'Addresses' by using getTables in DatabaseMetadata, but i m not getting proper results.

ResultSet rs1 = dbm.getTables("MYDB",null,"Addresses", null);

while(rs1.next()) {
String sch = rs1.getString("TABLE_SCHEM");
String table = rs1.getString("TABLE_NAME");
System.out.println("Name =" + table);
System.out.println("Schema =" + sch);
}

In MYDB database, I'm having three users 'dbo', 'alldata', 'user1'. My actual 'Addresses' table is in alldata and having views with name 'Addresses' in dbo and user1. Users 'alldata', 'user1' has no login names but user 'dbo' has login name 'sa'.

The above code just print:
Name = Addesses
Schema = dbo

but if I rename the view 'Adresses'in dbo i get:
Name = Addresses
Schema = alldata
Name = Addresses
Schema = user1

but if I change the line 2 in above code as:
ResultSet rs1 = dbm.getTables("MYDB","alldata","Addresses", null);

I get following output:
Name = Addresses
Schema = alldata

I just want to know why the code snipett above doesn't geive me required output i.e.
Name = Addesses
Schema = dbo
Name = Addresses
Schema = alldata
Name = Addresses
Schema = user1

[David Ulicny]

I think this features depends on driver which you are using. Better way is to use system catalogs to obtain database objects itself. For MSSQL look into the master database in system tables. There are also system stored procedures which helps you with system tables.