Count the Number of Tables in a SQL Server Database

sql-server tsql count sql-server-2012 database-table
Tot Zam picture Tot Zam · Aug 2, 2017 · Viewed 137.9k times · Source

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]

Answer

Tot Zam picture Tot Zam · Aug 2, 2017

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

Related questions

Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.

Read More
Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More