Count number of tables in sql server

Question

Prakash nidhi Verma · Answer

You can use INFORMATION_SCHEMA.TABLES for your database tables.
USE MyDatabaseand 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(*)and FROM sys.tables
OR
SELECT Count(*)and FROM andlt;DATABASE_NAMEandgt;.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Vrahmdev Tiwari · Answer

you can use this simple querySELECT COUNT(*) FROM SYS.TABLESNote: It will return you the count of the tables in the database.

Anonymous User · Answer

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

Abhishek Singh · Answer

you can use this simple queryUSE YOURDBNAMESELECT COUNT(*) FROM SYS.TABLES

Shankar M · Answer

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

Amit Singh · Answer

We count the tables in databasefor example:USE YOURDBNAMESELECT COUNT(*) from information_schema.tablesWHERE table_type = 'base table'Note: It will return you the count of the tables in the database.

Anonymous User · Answer

Hi Varun,
You can use below query to find total number of Table in your database.
select COUNT(*) from INFORMATION_SCHEMA.TABLES

forum

Count number of tables in sql server

Varun Agrawal

Can you answer this question?

7 Answers

Liked By