Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / count number of tables in sql server
Ask Question

Count number of tables in sql server

Varun Agrawal 70615 24 Mar 2011

Hi,

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

Thanks

mssql server mssql server
Updated 09 Jul 2018
Varun Agrawal
Varun Agrawal

Other

Message

7 Answers

Prakash nidhi Verma
Prakash nidhi Verma 09 Jul 2018
Report Abuse

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'
Vrahmdev Tiwari
Vrahmdev Tiwari 09 Jul 2018
Report Abuse

you can use this simple query
SELECT COUNT(*) FROM SYS.TABLES

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

Anonymous User
Anonymous User 25 Jan 2014
Report Abuse
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
Abhishek Singh 19 Jan 2014
Report Abuse
you can use this simple query

USE YOURDBNAME
SELECT COUNT(*) FROM SYS.TABLES
Shankar M
Shankar M 09 Feb 2013
Report Abuse 
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.
Amit Singh
Amit Singh 29 Mar 2011
Report Abuse
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.
Anonymous User
Anonymous User 24 Mar 2011
Report Abuse

Hi Varun,

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

select COUNT(*) from INFORMATION_SCHEMA.TABLES

advertising