How to find All Tables Ending with a Specific Suffix in SQL Server

Tutorialsrack 05/02/2025 SQL SQL Server

Introduction

When working with large databases in SQL Server, it is often necessary to find tables that follow a specific naming pattern. One such scenario is identifying all tables whose names end with a specific suffix. This can be useful for database maintenance, debugging, auditing, or dynamically querying related data.

This tutorial covers multiple ways to achieve this in SQL Server, catering to both beginners and advanced users.

Why Find Tables by Suffix in SQL Server?

Finding tables with a specific suffix can help in:

• Schema Management – Identifying related tables, such as all archived tables ending with _archive.
• Data Maintenance – Finding backup or temporary tables ending with _temp.
• Automating Queries – Dynamically generating SQL scripts based on table naming conventions.
• Performance Optimization – Quickly filtering out unnecessary tables when analyzing a database.

Methods to Find Tables by Suffix

SQL Server provides multiple ways to search for table names that end with a specific suffix. Below are three commonly used methods.

1. Using INFORMATION_SCHEMA.TABLES

The INFORMATION_SCHEMA.TABLES view contains metadata about all tables in the database, making it a straightforward option to find tables by suffix.

Example:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_archive';

2. Using sys.tables

The sys.tables system catalog view provides another method to retrieve table names based on suffix filtering.

Example:

SELECT name
FROM sys.tables
WHERE name LIKE '%_archive';

3. Using sys.objects

The sys.objects system catalog view stores all database objects, including tables. This method is particularly useful if you need more metadata about the tables.

Example:

SELECT name
FROM sys.objects
WHERE type = 'U'
AND name LIKE '%_archive';

Here, 'U' represents user tables.

Detailed Examples with Explanations

Let’s consider a database with the following tables:

• sales_archive
• customer_archive
• orders
• employee_temp

If we execute the following query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_archive';

We get:

TABLE_NAME
------------
sales_archive
customer_archive

This confirms that only the tables ending with _archive are retrieved.

Real-World Examples and Use Cases

1. Identifying Backup Tables

Organizations often store old data in archive tables for auditing purposes. You can quickly list all archive tables using:

SELECT name
FROM sys.tables
WHERE name LIKE '%_backup';

2. Managing Temporary Tables

Developers sometimes create temporary tables ending with _temp for staging data. To find such tables:

SELECT name
FROM sys.tables
WHERE name LIKE '%_temp';

3. Automating SQL Queries

If you need to drop all tables ending with _temp, you can generate dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'DROP TABLE ' + name + '; '
FROM sys.tables
WHERE name LIKE '%_temp';
EXEC sp_executesql @sql;

Key Takeaways

• SQL Server provides multiple ways to search for tables by suffix, including INFORMATION_SCHEMA.TABLES, sys.tables, and sys.objects.
• The LIKE operator is essential for pattern matching in table names.
• These techniques help with database maintenance, query automation, and performance optimization.
• Dynamic SQL can be used for bulk operations based on table suffix patterns.

Summary

Finding tables with a specific suffix in SQL Server is a crucial task for database administrators and developers. Using INFORMATION_SCHEMA.TABLES, sys.tables, and sys.objects, we can efficiently retrieve and manage tables based on naming conventions. These techniques help in organizing and optimizing database operations while making querying more efficient.

Related Posts