CODEWITHSUNDEEP
sqlsql-server-2005
anonymous
anonymous

Reputation: 2087

sql or trick to search through whole database

is there a way to actually query the database in a such a way to search for a particular value in every table across the whole database ?

Something like a file search in Eclipse, it searches accross the whole worspace and project ?

Sorry about that .. its MS SQL 2005

Upvotes: 1

Views: 254

Answers (5)

Thomas Allier
Thomas Allier

Reputation: 21

In phpmyadmin, go to your database, reach the search tab.

Here you will be able to select all of your tables and search through your entire db in one time.

Upvotes: 0

user330315
user330315

Reputation:

SQL Workbench/J has a built in tool and command to do that.

It's JDBC based and should also work with SQL Server.

Upvotes: 1

mohsensajjadi
mohsensajjadi

Reputation: 335

Well, your best bet is to write a procedure to do this. But to give you some pointers you can use the INFORMATION_SCHEMA.Tables to get a list of all the tables in a given database and INFORMATION_SCHEMA.Columns to get a list of all columns. These tables also give you the datatype of columns. So you will need a few loops on these tables to do the magic.

It should be mentioned most RDBMSs nowadays support these schemas.

Upvotes: 0

techie_28
techie_28

Reputation: 2133

In mysql you can use union operator like

(SELECT * from table A where name = 'abc') UNION (SELECT * from table B where middlename = 'pqr')

and so on use full text search for efficency http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 0

Jan S
Jan S

Reputation: 1837

You will need to use the LIKE operator, and search through each field separately. i.e. 

    SELECT * FROM <table name> 
    WHERE (<field name1> LIKE '%<search value>%') OR
           (<field name2> LIKE '%<search value>%') OR

... etc.

This isn't a quick way though.

I think the best way would be to
1) programatically generate the query and run it
2) use a GUI tool for the SQL server you are using which provides this functionality.

Upvotes: 0

Related Questions