How to Find All Columns 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 columns that follow a specific naming pattern. One such scenario is identifying all columns 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 Columns by Suffix in SQL Server?

Finding columns with a specific suffix can help in:

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

Methods to Find Columns by Suffix

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

1. Using INFORMATION_SCHEMA.COLUMNS

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

Example:

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%_date';

2. Using sys.columns

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

Example:

SELECT c.name AS ColumnName, t.name AS TableName 
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%_date';

3. Using sys.objects and sys.columns

The sys.objects and sys.columns system catalog views store database objects and columns, respectively. This method is useful when more metadata is required.

Example:

SELECT c.name AS ColumnName, o.name AS TableName 
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.type = 'U' 
AND c.name LIKE '%_date';

Here, 'U' represents user tables.

Detailed Examples with Explanations

Let’s consider a database with the following tables and columns:

If we execute the following query:

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%_date';

We get:

COLUMN_NAME    TABLE_NAME
-------------  -----------
hire_date      Employees
order_date     Orders
expiry_date    Products

This confirms that only the columns ending with _date are retrieved.

Real-World Examples and Use Cases

1. Identifying Date Columns

Organizations often store date-related data with a common suffix like _date. You can quickly list all date-related columns using:

SELECT name 
FROM sys.columns 
WHERE name LIKE '%_date';

2. Managing Temporary Columns

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

SELECT c.name AS ColumnName, t.name AS TableName 
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%_temp';

3. Automating SQL Queries

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

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'ALTER TABLE ' + t.name + ' DROP COLUMN ' + c.name + '; ' 
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%_temp';
EXEC sp_executesql @sql;

Key Takeaways

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

Summary

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

Related Posts