Search In complete database

Question

I like to build a query which can search in complete data base in all tables.

Ex. Search String: 9049 Now in result I need search result from all table where this string matches. Simply I can't write LIKE to all field of table.

Thanks In Advance.

what platform/tool are using to interact with your database? — Ashwani Goyal, Mar 31 '16 at 06:04
@SantoshRamKunjir Nop. Till time I have used search for specific field only. This is first time I faced. So not understanding what to do. — prashant.fepale, Mar 31 '16 at 06:05
The same question is been asked already http://stackoverflow.com/questions/8435963/how-to-find-a-string-inside-a-entire-database. You need to blend it in CI — Ashwani Goyal, Mar 31 '16 at 06:09
Here is a link to a post , he is updating but i think you can manage to select http://stackoverflow.com/a/10182498/3568847 :) — Praveen Kumar, Mar 31 '16 at 06:19

score 0 · Answer 1 · answered Mar 31 '16 at 06:02

You could do an SQLDump of the database (and its data) then search that file. or You can peek into the information_schema table. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.

score 0 · Answer 2 · answered Mar 31 '16 at 06:20

You could first read out all the tables:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'yourdatabasename'

and then loop through all the tables and get all the columns:

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'yourdatabasename' AND table_name = 'thetablenamefromyourloop'

and then build a dynamic statement thats builds the WHERE clause based on your searchterm.

or to get just all the information just read out everything. This contains all the information about all the tables and columns, which might be useful to just search in all the varchar and text fields etc.

SELECT * FROM information_schema.columns
WHERE table_schema = 'yourdatabasename'

Search In complete database

2 Answers2