Post

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.

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

How about with ‘cheetah’.

1
2
3
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.

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

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

1
2
3
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.

1
2
3
4
5
6
7
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!

This post is licensed under CC BY 4.0 by the author.

Comments powered by Disqus.

© Kevin Schwickrath. Some rights reserved.

Using the Chirpy theme for Jekyll.