---
title: "Count number of tables in sql server"  
description: "Count number of tables in sql server"  
author: "Varun Agrawal"  
published: 2011-03-24  
updated: 2018-07-09  
canonical: https://www.mindstick.com/forum/204/count-number-of-tables-in-sql-server  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# Count number of tables in sql server

Hi,

I have a [requirement](https://yourviews.mindstick.com/view/85169/becoming-an-influencer-requirement-of-skills-and-knowledge) where I want to [count](https://www.mindstick.com/forum/157774/selecting-count-with-distinct) no. of [tables](https://www.mindstick.com/articles/336597/introduction-of-html-tables-for-web-development) [present](https://answers.mindstick.com/qa/96635/explain-about-the-various-features-present-in-ms-access) in Database.

Thanks

## Replies

### Reply by Prakash nidhi Verma

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'
```

### Reply by Vrahmdev Tiwari

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

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

### Reply by Anonymous User

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
```

### Reply by Abhishek Singh

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

### Reply by Shankar M

```
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.
```

### Reply by Amit Singh

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.

### Reply by Anonymous User

Hi Varun,

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

select COUNT(*) from INFORMATION_SCHEMA.TABLES


---

Original Source: https://www.mindstick.com/forum/204/count-number-of-tables-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.