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!
Comments powered by Disqus.