Count number of tables in sql server

Total Post:3

Points:21
 67179  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:140

    Points:984
    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