aspose file tools*
The moose likes JDBC and the fly likes Tables Count in Sql Server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Tables Count in Sql Server" Watch "Tables Count in Sql Server" New topic
Author

Tables Count in Sql Server

Suji N
Ranch Hand

Joined: Sep 04, 2001
Posts: 35
Hi Friends
I have a doubt.
How to display no of tables existed in Database[tablecount]
i.e., table count
if possible pl send me a sample code
thanks inadvance
yours
Suji N
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
Not sure there is a method that gives number of tables. But you can get a list of tables, and you can determine your count from there. This prints tablenames, but you can get your count without even printing the tables.....
Bosun
-----------------------------
DatabaseMetaData myMT = conn.getMetaData();
String[] myTables = {"TABLE"};
ResultSet tables = myMT.getTables(null,
null, "%", myTables);
String tableName = null;

while (tables.next())
{
tableName = tables.getString("TABLE_NAME");
System.out.println(tableName);
}


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
Not sure there is a method that gives number of tables. But you can get a list of tables, and you can determine your count from there. This prints tablenames, but you can get your count without even printing the tables.....
Bosun
-----------------------------
DatabaseMetaData myMT = conn.getMetaData();
String[] myTables = {"TABLE"};
ResultSet tables = myMT.getTables(null,
null, "%", myTables);
String tableName = null;

while (tables.next())
{
tableName = tables.getString("TABLE_NAME");
System.out.println(tableName);
}
Suji N
Ranch Hand

Joined: Sep 04, 2001
Posts: 35
Thankyou VeryMuch Mr. Bello
The tables are displaying with ur code
Pl send me a small code which will displays the no of tables with ur code.
thanksinadvance
Yours
Suji
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
there is a sql statement which unfortunately is on my pc at home so i don't have it here, but there is a dictionary table in the sql server with the names of all tables, columns, fields, indices etc in, and it's easy to query for whatever info you need. i'll try to remember to post it tonight.

I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
Just declare a variable and update it in your loop.
int tableCnt = 0;
while (tables.next())
{
tableName = tables.getString("TABLE_NAME");
++tableCnt;
System.out.println(tableName);
}

Bosun
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

The best way to count all the tables in a database would be to use a database query on the data dictionary. Use this with caution because each database has their own way of doing this(which means code is not portable). But is you are looking for performance this may be the way to go because database metadata is a VERY EXPENSIVE operation.
In Oracle, the query would be
"SELECT COUNT( table_name ) FROM dba_tables WHERE owner = ?"
use the owner criteria in the where clause to eliminate counting system tables
If your database is not Oracle, then you'll have to check your database documentation for instruction.
Jamie
Suji N
Ranch Hand

Joined: Sep 04, 2001
Posts: 35
Hai Jamie
Thankyou VeryMuch
I am using Sql Server
When i am trying to execute the following sql stmt in sql server
like
SELECT COUNT( table_name ) FROM dba_tables WHERE owner = kumar
it is saying invalid objectname 'dba_tables' error
Pl send me the sql stmt in sql server 2000
Thankyou Verymuch
Yours
Suji
Originally posted by Jamie Robertson:
The best way to count all the tables in a database would be to use a database query on the data dictionary. Use this with caution because each database has their own way of doing this(which means code is not portable). But is you are looking for performance this may be the way to go because database metadata is a VERY EXPENSIVE operation.
In Oracle, the query would be
"SELECT COUNT( table_name ) FROM dba_tables WHERE owner = ?"
use the owner criteria in the where clause to eliminate counting system tables
If your database is not Oracle, then you'll have to check your database documentation for instruction.
Jamie

z meng
Greenhorn

Joined: Oct 11, 2001
Posts: 10
please,try it.
select count(*) from sysobjects where type='T'
Suji N
Ranch Hand

Joined: Sep 04, 2001
Posts: 35
Hai zmeng,
This following one is not working in sql server database
select count(*) from sysobjects where type='T'[/B]
pl check it out and send me
thankyou very much

z meng
Greenhorn

Joined: Oct 11, 2001
Posts: 10
Sorry Suji N,
please do this:
select count(*) into i from sysobjects where type='u'
then
i=i-1
here "i" is tables count in sql server2000
good luck!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Tables Count in Sql Server