Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Tables Count in Sql Server

 
Suji N
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Bello
Ranch Hand
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Bosun Bello
Ranch Hand
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
please,try it.
select count(*) from sysobjects where type='T'
 
Suji N
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic