Have you ever encountered a scenario where you needed to retrieve all the tables in a SQL Server database that contain a specific string in their name? This is a common requirement in the industry, especially when dealing with databases that follow a specific naming convention.

Let’s consider a scenario where an organization initially adopted a naming convention where they prefixed the string ‘tbls_’ before every table name. However, with the arrival of a new manager, the decision was made to remove this static prefix. Now, the task at hand is to retrieve a list of all the tables where the name contains the string ‘tbls_’ anywhere in the table name.

To accomplish this, we can use a simple script in SQL Server:

DECLARE @SearchString VARCHAR(256)
SET @SearchString = 'tbls_%' -- Change SearchString

SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND crdate <= DATEADD(m, -1, GETDATE())
AND name LIKE '%' + @SearchString + '%'

In this script, we declare a variable @SearchString and set it to the desired string we want to search for in the table names. The LIKE keyword is used with a % sign before and after the search string, allowing us to match any occurrence of the string within the table name.

It’s important to note that using the LIKE keyword with wildcards can impact performance, as it may require scanning all the table names in the database. However, it guarantees accurate results.

You can modify the LIKE condition based on your specific business needs. For example, if you want to search for tables that start with ‘tbls_’, you can change the @SearchString variable to 'tbls_%'.

By using this script, you can easily retrieve a list of all the tables in your SQL Server database that contain a specific string in their name. This can be helpful for various tasks, such as data analysis, database maintenance, or even during job interviews.

For more advanced scenarios, you can explore other techniques such as using cursors or dynamic SQL. However, for most cases, the simple script provided above should suffice.

Remember, understanding how to query and manipulate table names in SQL Server is an essential skill for any database professional. It allows you to efficiently manage and work with your database objects.

So, the next time you need to list tables with a specific string in their name, give this script a try and see how it simplifies your task!