Search code examples
sqlsql-serverschemakeyword-search

Search SQL Server Database by Keyword to Return the keyword, table, and dbo

I'm trying to recall an older query I used to easily locate column names in large databases on SQL server. For example I'm looking for a keyword '%specialist%' to see how many tables it belongs to and what those tables are labeled as so I can write queries from those tables containing the keyword. I scanned through similar questions but the answers didn't yield results. Maybe I don't have access to all the tables? But I know I have enough access to bring that keyword back for 6-10 different tables or views. Can any assist me?

This is what I thought might be right:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'specialists%'

This is what I thought might be right: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'specialists%'

Results were blank

Tried an alternative:

SELECT OBJECT_NAME(object_id) FROM sys.columns WHERE name = 'specialist%'

Results were blank.

Solution

  • Use like when using '%%' not =

     select * from INFORMATION_SCHEMA.COLUMNS 
   where COLUMN_NAME like '%specialist%'