Home > DeveloperSection > Forums > Count number of tables in sql server
Varun Agrawal
Varun Agrawal

Total Post:3

Points:21
Posted on    March-24-2011 8:13 AM

 MSSQL Server MSSQL Server 
Ratings:


 5 Reply(s)
 58677  View(s)
Rate this:

Hi,

I have a requirement where I want to count no. of tables present in Database.

Thanks



Alex R
Alex R

Total Post:40

Points:280
Posted on    March-24-2011 8:15 AM

Hi Varun,

You can use below query to find total number of Table in your database.

select COUNT(*) from INFORMATION_SCHEMA.TABLES


Modified On Mar-24-2011 08:16:30 AM

Amit Singh

Total Post:103

Points:721
Posted on    March-29-2011 8:23 AM

We count the tables in database

for example:
USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'


Note: It will return you the count of the tables in the database.

Shankar M

Total Post:25

Points:175
Posted on    February-09-2013 2:07 AM

You can also query 
SELECT *
  FROM SYS.objects
 WHERE TYPE = 'U' to get the tables associated with a particular user in the database. Here TYPE='U' is the User Tables.

Abhishek Singh

Total Post:8

Points:56
Posted on    January-19-2014 10:38 PM

you can use this simple query

USE YOURDBNAME

SELECT COUNT(*) FROM SYS.TABLES

Dev Patel

Total Post:26

Points:182
Posted on    January-25-2014 11:47 PM

You can count both tables and stored procedures by following query:
SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
        WHEN 'S'
            THEN 'System Tables'
        WHEN 'IT'
            THEN 'Internal Tables'
        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
    END, 
    COUNT(*)     
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE

Don't want to miss updates? Please click the below button!

Follow MindStick