SQL Search Engine for whole database

Question

I have a situation where I will need a search engine for database, like a text search or lets say like a google search engine where I can put any text and get the result from the database based on it. In the search box I can put anything like name or date of birth or address and get the result from the database based on it.

This is very new to me. Is there any certain way to solve this kind of problem or certain route that I can go?

Are you saying you want to find a given value from any column in any table in the entire database? — Sean Lange, Sep 13 '19 at 14:04
Possible duplicate of [How to search sql server database for string?](https://stackoverflow.com/questions/9185871/how-to-search-sql-server-database-for-string) — Amira Bedhiafi, Sep 13 '19 at 14:15
What are you hoping to accomplish with this? What's the business case? If you're searching for something (such as an address) why would you need to search every table, column and row of your database? Unless I'm missing something here you would write standard SQL to query against the address columns as defined in your schema. Post back a little more of what you're hoping to accomplish. — JazzmanJim, Sep 13 '19 at 15:50

score 1 · Answer 1 · answered Sep 13 '19 at 15:08

If performance is NOT paramount, here is an option that uses a bit of XML and dynamic SQL

TO BE CLEAR, I would not suggest this on large databases

Example

Declare @Search varchar(max)= '02806'
Declare @SQL    varchar(max) = ''

Select @SQL = @SQL + '
Union All 
Select TableName='''+quotename(table_name)+''',Record=convert(xml,(Select A.* for XML Raw)) 
 From '+quotename(table_schema)+'.'+quotename(table_name)+' A 
 Where (Select A.* for XML Raw) like ''%'+@Search+'%''
'
 From INFORMATION_SCHEMA.Tables 
 Where Table_Type='BASE TABLE' 
   and Table_Name like 'OD%'  -- <<< I would suggest using a reasonable test filter first

Set @SQL = stuff(@SQL,1,11,'')

--Print @SQL
Exec(@SQL)

Returns

score 0 · Answer 2 · answered Sep 13 '19 at 14:32

I got this from a friend:

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)


SET @search_string = 'INV2226'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239,106,62,60)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

The result will tell you in which table it is and what column.

Hope it helps.

SQL Search Engine for whole database

2 Answers2