List names of all tables in a SQL Server 2012 schema

sql

metadata

database-optimizations

sql-queries

byAlex Kataev·Oct 29, 2024

Get all table names in a particular SQL Server 2012 schema with:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'YourSchema' AND TABLE_TYPE = 'BASE TABLE';

Replace 'YourSchema' with your actual schema name. This statement returns only actual tables, omitting views.

Refined, compressed knowledge for metadata and optimizations

INFORMATION_SCHEMA views are standardized in SQL databases, offering a robust strategy to access metadata for your database elements. Utilizing INFORMATION_SCHEMA views over querying system tables directly ensures compatibility and accuracy across different versions and platforms.

-- Sorting tables names, because we like things tidy, right?
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'YourSchema' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

For a deeper dive into ownership, creation dates, join INFORMATION_SCHEMA views with other system views:

-- Inner join: It's like a party, but only for matching guests
SELECT t.name AS TableName, s.name AS SchemaName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = N'YourSchema' AND t.is_ms_shipped = 0
ORDER BY SchemaName, TableName;

Replace N'YourSchema' with your own schema name keeping in mind that t.is_ms_shipped = 0 filters out system tables.

Always prefix your queries with:

USE database_name;
GO

While USE confirms the correct database is being targeted, GO ensures batch execution of your commands.

Exploring deeper: scenarios and use cases

Views: include, exclude

Including views:

-- Invite everyone to the party
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'YourSchema';

Excluding views:

-- No views allowed, it's TABLES ONLY night!
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'YourSchema' AND TABLE_TYPE = 'BASE TABLE';

Dealing with multiple schemas

When multiple schemas are in play, a proper filter is key:

-- Into the schema jungle we go!
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA IN ('Schema1', 'Schema2') AND TABLE_TYPE = 'BASE TABLE';

Merging databases

For cross-database queries, employ fully qualified names and join conditions:

-- When two databases love each other very much...
SELECT t.TABLE_NAME, s.TABLE_SCHEMA 
FROM YourDatabase1.INFORMATION_SCHEMA.TABLES s
JOIN YourDatabase2.INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE s.TABLE_TYPE = 'BASE TABLE';