Interview Question of the Week #005 – List All the Tables with Name Containing Specific String

The other day, I received an email from a blog reader. When he appeared for an interview, he was asked to retrieve all the tables where the name of the table contained the words ‘tbls_’. This was a very interesting scenario. As the organization had earlier adopted a specific naming convention where they were writing the string ‘tbls_’ before any table name. However, as the new manager joined the organization, he decided to remove this static prefix and wanted the list of the all the tables where the name of the table contained ‘tbls_’ anywhere in the table. I think this is a very common question and we often see this particular requirement in the industry.

Question: How do you list all the tables in the database where the name of the table contains specific strings like ‘tbls_’ anywhere in the name of the table.

Answer: Here is a simple script which can list all the tables with specific string in the name of the table.

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+'%'

Remember this script is using LIKE keyword with a % sign before and after searching string, hence it may be a bit slower in performance, but will for sure return accurate results. You can change the like condition based on your business need.

Here is the similar script which I had written a few years ago with the help of the cursor SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created.

Reference: Pinal Dave (https://blog.sqlauthority.com)