[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Find a a string in database

Posted on 2010-04-01
7
Medium Priority
?
452 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:gautam_reddyc
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29364478
>>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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 29366160
0
 

Author Comment

by:gautam_reddyc
ID: 29366202
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 11

Expert Comment

by:dougaug
ID: 29366617
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
 

Accepted Solution

by:
gautam_reddyc earned 0 total points
ID: 29367131
http://code.google.com/p/anywhereindb/

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

Thanks guys!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29516970
>>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
 
LVL 21

Expert Comment

by:K V
ID: 29889261
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question