Count the Number of Tables in a SQL Server Database

Question

I have a SQL Server 2012 database called MyDatabase. How can I find how many tables are in the database?

I'm assuming the format of the query would be something like the following, but I don't know what to replace database_tables with:

USE MyDatabase
SELECT COUNT(*)
FROM [database_tables]

Solution 1

You can use INFORMATION_SCHEMA.TABLES to retrieve information about your database tables.

As mentioned in the Microsoft Tables Documentation:

INFORMATION_SCHEMA.TABLES returns one row for each table in the current database for which the current user has permissions.

The following query, therefore, will return the number of tables in the specified database:

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

---

As of SQL Server 2008, you can also use sys.tables to count the the number of tables.

From the Microsoft sys.tables Documentation:

sys.tables returns a row for each user table in SQL Server.

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

SELECT COUNT(*)
FROM sys.tables

Solution 2

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

to get table counts

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'dbName';

this also works

USE databasename;
SHOW TABLES;
SELECT FOUND_ROWS();

Solution 3

Try this:

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

Comments

[GarethD]

A good article on the two sources is this one The case against INFORMATION_SCHEMA views, and highlights a few reasons why you might use the system catalog over the INFORMATION_SCHEMA. Also, it is pedantic I know, but sys.tables was introduced in SQL Server 2005

[ransems]

Did not work for me. Maybe i'm missing something?

[Erick de Vathaire]

@ransems , replace "<DATABASE_NAME>" with the name of your db: select db_name()