List All Tables

Script: scripts/utilities/list_all_tables.sql

Overview

This utility script provides a simple query to list all base tables in the current database. It retrieves table schema and table name information from the SQL Server information schema, making it useful for database exploration and documentation purposes.

Parameters

This script does not require any parameters.

Data Components

The script returns a simple result set with:

TABLE_SCHEMA - The schema name containing the table
TABLE_NAME - The name of the table

Output Format

Column	Data Type	Description
TABLE_SCHEMA	nvarchar	Schema name (e.g., 'dbo')
TABLE_NAME	nvarchar	Table name

Technical Implementation

The script uses:

INFORMATION_SCHEMA.TABLES system view
Filters for TABLE_TYPE = 'BASE TABLE' to exclude views and system tables
Results ordered by schema and table name for consistent output

Notes

This script works on any SQL Server database
Only returns base tables (excludes views, temporary tables, and system tables)
Useful for database documentation and exploration
Can be modified to include additional table metadata if needed

T-SQL

list_all_tables.sql

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Content Inventory

Doc File: content/docs/utilities/list_all_tables.mdx
SQL Script: SQL/utilities/list_all_tables.sql

List All Tables

On this page