SQL Search Objects Names

Let’s say you have several databases on a Microsoft SQL Server, each with loads of tables. Now you want to find a table with the word ‘users’ in the table name. Easy, run a SQL query to search the table names.

Super simple!

Search SQL tables names with this query.

SELECT *
FROM sys.tables
WHERE name LIKE '%users%'

How about with ‘cheetah’.

SELECT *
FROM sys.tables
WHERE name LIKE '%cheetah%'

The percent symbol ’%’ on either side of the word is a wildcard. As in match anything_users_anything or anything_cheetah_anything.

You can take the same query and search other SQL server other objects.

With this query I search ‘sys.views’ for anything containing ‘users’ in the name.

SELECT *
FROM sys.views
WHERE name LIKE '%users%'

This query searches ‘sys.procedures’ for anything containing ‘users’ in the name.

SELECT *
FROM sys.procedures
WHERE name LIKE '%users%'

With this query I’m searching for “code” in all the table and column names of my selected database.

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 '%users%'
ORDER BY TableName, ColumnName

Happy databasing!