COUNT NUMBER OF TABLES IN SQL SERVER

Total Post:3

Points:21
 66729  View(s)
Ratings:
Rate this:

Hi,

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

Thanks

  1. 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 Apr-04-2018 01:52:50 AM
  1. 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.

      Modified On Apr-04-2018 01:53:05 AM
  1. 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.

      Modified On Apr-04-2018 01:53:30 AM
  1. 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. 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

  1. Post:3

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

    you can use this simple query

    SELECT COUNT(*) FROM SYS.TABLES

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

  1. Post:141

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

    You can use INFORMATION_SCHEMA.TABLES for your database tables.

    USE MyDatabase 
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    The following query will also return the number of table in your database:

    SELECT COUNT(*) 
    FROM sys.tables

    OR

    SELECT Count(*) 
    FROM <DATABASE_NAME>.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

Answer

NEWSLETTER

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