?
Solved

SQL Query to count the number of tables in a DB

Posted on 2007-09-28
5
Medium Priority
?
7,461 Views
Last Modified: 2008-01-09
Does anyone have a query to count the number of tables in a database?  Doing some analysis here...

TIA
0
Comment
Question by:dstjohnjr
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19981862
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
 
LVL 8

Expert Comment

by:Mikkk
ID: 19981872
PHP+MySQL:
$sql = "SHOW TABLES";
$result = mysql_query($sql);
$num_of_tables = mysql_num_rows($result);
0
 

Author Comment

by:dstjohnjr
ID: 19981966
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19981991
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
 

Author Comment

by:dstjohnjr
ID: 19982064
Right on angellll.  That did it!  Thanks much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question