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)

Solarwinds
Previous Post
SQL SERVER – 8 Performance Related Articles on Logical Reads
Next Post
SQL SERVER – How Can Deadlock Happen on Same Table?

Related Posts

No results found

4 Comments. Leave new

  • hi pinal by using this script i am getting the information of system tables only. not the user defined tables.

    Reply
  • shubham chawla
    February 1, 2015 1:47 pm

    You can directly use

    Select * from sys.tables where name like ‘%searchstring%’

    Reply
  • Phaneendra Babu Subnivis
    February 2, 2015 10:20 am

    We can also make use of sys.tables if the query to to get all user defined tables created in the database.

    Regards,
    Phaneendra

    Reply
  • Hi Pinal,

    Assuming the requirement was to get only the table names & not the created dates as well, wanted to know whether the below query would be simpler?

    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘tbls_%’ AND TABLE_TYPE = ‘BASE TABLE’

    Reply

Leave a Reply

Menu