COUNT NUMBER OF TABLES IN SQL SERVER

Varun Agrawal

Total Post:3

Points:21
Posted by  Varun Agrawal
 64748  View(s)
Ratings:
Rate this:

Hi,

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

Thanks

  1. Alex R

    Post:40

    Points:280
    Re: Count number of tables in sql server

    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
  1. Amit Singh

    Post:103

    Points:721
    Re: Count number of tables in sql server

    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.

  1. Shankar M

    Post:25

    Points:175
    Re: Count number of tables in sql server

    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.

  1. Abhishek Singh

    Post:8

    Points:56
    Re: Count number of tables in sql server

    you can use this simple query


    USE YOURDBNAME

    SELECT COUNT(*) FROM SYS.TABLES

  1. Dev Patel

    Post:26

    Points:182
    Re: Count number of tables in sql server

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!