Find a a string in database

Hello, I have an MVC framework and I am not sure which queries are dumping the data into which tables. I am only aware of the database. So as a process of reverse engineering, I am trying to insert a few marker values and try to retreive the tables into which they were inserted by using these marker values. But now I want to know if this is possible??

Let us consider a database as a folder, so is a recursive search possible in a database, which would skim through all the tables, and their columns in the database and return the result where some column value matches the marker. I know this is a tedious process, this is only part of reverse engineering and would not be in the production instance.
gautam_reddycAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>Let us consider a database as a folder

I don't understand.  

You've posted this in three totally different database zones.  What database product do you want this info for?
0
Aneesh RetnakaranDatabase AdministratorCommented:
0
gautam_reddycAuthor Commented:
What I meant by that was, in linux/unix environment, we have a command called grep for search, and a recursive search helps to search a pattern of string in all files available in that folder. I was just giving an example.


I am using a MySQL server. But normally sql code with little modification should work in others too.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dougaugCommented:
I've written the following procedure, maybe it helps you (it is for Sybase ASE, but I think this work in MSSQLServer with some changes).

It outputs one line for every table and column in your database as this:

SELECT * FROM TABLE WHERE COLUMN LIKE '%YOUR_MARKER%'

Execute the procedure passing the marker as a parameter and then execute the commands that were 'printed' by the procedure.


create procedure proc1 @marker varchar(255) as
begin

declare @table_name varchar(255),
        @table_id   int,
        @column_name varchar(255)

declare cur_tables cursor
    for select name, id from sysobjects
         where type = 'U'
         order by name

declare cur_columns cursor
    for select name from syscolumns
         where id = @table_id
         order by name

open cur_tables

fetch cur_tables into @table_name, @table_id

while @@sqlstatus = 0
begin
  open cur_columns

  fetch cur_columns into @column_name

  while @@sqlstatus = 0
  begin
    select "SELECT * FROM " + @table_name + " WHERE " + @column_name + " LIKE '%" + @marker + "%'"
    fetch cur_columns into @column_name
  end
 
  close cur_columns

  fetch cur_tables into @table_name, @table_id
end

select @table_name, @table_id

close cur_tables

deallocate cursor cur_tables
deallocate cursor cur_columns

end

go
0
gautam_reddycAuthor Commented:
http://code.google.com/p/anywhereindb/

that is the solution!! Kudos to the author!!

Thanks guys!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Zones: MySQL Server, MS SQL Server, PL / SQL<<
In future please select your zones appropriately.  MS SQL Server and PL / SQL do not apply.  I know it is tempting, but you are really wasting your time if you are unable to use it.
0
theGhost_k8Database ConsultantCommented:
As such the question is closed but  I'd like to add another solution:
http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.