MSSQL Query to Count Tables in a Schema

This query demonstrates how to count the number of tables belonging to a specific schema in a Microsoft SQL Server database. The example below focuses on the dbo schema, commonly used as the default schema in many SQL Server installations.

The Query

SELECT COUNT(*)
FROM information_schema.tables ist
WHERE ist.table_type = 'base table'
  AND ist.TABLE_SCHEMA = 'dbo';

Explanation

1. information_schema.tables: This system view provides metadata about all tables in the database, including views and base tables.

2. Filter: ist.table_type = 'base table': Ensures that only base tables (excluding views) are counted.

3. Filter: ist.TABLE_SCHEMA = 'dbo': -Restricts the results to the specified schema, in this case, dbo.

4. count(*): Returns the total count of rows that match the filters, corresponding to the number of tables in the schema.

Usage Scenario

This query is useful for:

• Database maintenance tasks.
• Auditing the number of tables in a specific schema.
• Checking schema-specific table usage in multi-schema databases.

Example Output

Suppose the dbo schema contains 5 tables. The query will return:

Count 5

Customization

• To count tables in a different schema, replace ‘dbo’ in the WHERE clause with the desired schema name:

SELECT COUNT(*)
FROM information_schema.tables ist
WHERE ist.table_type = 'base table'
  AND ist.TABLE_SCHEMA = 'your_schema_name';

• To list table names instead of counting, modify the query:

SELECT ist.TABLE_NAME
FROM information_schema.tables ist
WHERE ist.table_type = 'base table'
  AND ist.TABLE_SCHEMA = 'dbo';

This query is a quick and effective tool for database administrators and developers to monitor and manage schema-specific tables in MSSQL.