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

A reader wrote to me after an interview. His company used tbls_ at the start of table names, then a new manager decided to remove that prefix. The interview question was to find every table that still contained tbls_ anywhere in its name. This is a useful little question because the underscore changes the meaning of a careless LIKE search.

Which tables contain the literal text tbls_?

Run this in the database you want to inspect. CHARINDEX treats the underscore as an ordinary character, and joining to sys.schemas keeps two tables with the same name in different schemas distinct.

DECLARE @Fragment nvarchar(128) = N'tbls_';

SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE CHARINDEX(@Fragment, t.name) > 0
ORDER BY s.name, t.name;

The older version of my query used sysobjects, treated _ and % as part of a LIKE pattern, and excluded tables created within the last month. That date filter had nothing to do with the reader’s question. A recently renamed table should appear in the answer too.

If you prefer LIKE

Inside a LIKE pattern, an unescaped underscore matches any single character. Search for a literal underscore with [_]:

SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name LIKE N'%tbls[_]%'
ORDER BY s.name, t.name;

If the interviewer intends a wildcard pattern rather than the exact prefix characters, say so and use that pattern deliberately. That one clarification is the difference between finding the old naming convention and returning unrelated names.

For an older, date-based table search, see my earlier cursor example. The name search here does not need a cursor.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.