Count the Number of Tables in a SQL Server Database
Try this:
SELECT Count(*)
FROM <DATABASE_NAME>.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
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
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();