• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7465
  • Last Modified:

SQL Query to count the number of tables in a DB

Does anyone have a query to count the number of tables in a database?  Doing some analysis here...

TIA
0
dstjohnjr
Asked:
dstjohnjr
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
assuming sql server 2005:
select count(*) from sys.tables

with sql 2000 or lower:
select count(*) from sysobjects where xtype = 'U'
or
select count(*) from information_schema.tables

0
 
MikkkCommented:
PHP+MySQL:
$sql = "SHOW TABLES";
$result = mysql_query($sql);
$num_of_tables = mysql_num_rows($result);
0
 
dstjohnjrAuthor Commented:
Thanks angellll, your solution is working good.  Now, how about an additional statement that counts the number of tables with a certain character trailing  in the table name, for instance TABLENAME_?  I want to count the number of tables that have the underscore character at the very end of their name.  That should do it.

Thanks again!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
assuming sql server 2005:
select count(*) from sys.tables where name like '%[_]'

with sql 2000 or lower:
select count(*) from sysobjects where xtype = 'U' and name like '%[_]'
or
select count(*) from information_schema.tables where table_name like '%[_]'

0
 
dstjohnjrAuthor Commented:
Right on angellll.  That did it!  Thanks much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now