DB : Searching all rows in all columns in entire DB

[Drakkon]

Is there an SQL method of searching all tables in a given DB for one specific value?

 

[Apathetic]

You're going to have to specify each column you want to search in your SQL query.

So given C1, C2, C3,... Cn (which are the names of all your columns in table T) you'd have to write something like:

SELECT * FROM T WHERE c1='value' or c2='value' or c3='value' or .... or cn='value'

Plan on this query taking a LONG time to run.

Dave

 

[Armitage]

If you have to search every column of every table, your DB design is seriously fvcked

 

[DT4K]

Originally posted by: Armitage
If you have to search every column of every table, your DB design is seriously fvcked

That was my first thought as well. I'm curious why you would want to do that. But you could put together a pretty nasty query that used cursors and built dynamic sql to to it.

In MS SQL 2000 for instance, you can get a list of all the user tables with:
select * from sysobjects where xtype = 'U'

And you can get a list of all the columns and stored procedure parameters from the syscolumns table.
In the syscolumns table, each record has an id field that tells you which table or sproc that column belongs to.

So with those two tables, you should be able to build nested cursors that go through each table and with each table, go through each column that is part of that table and do a select for your value.
Definitely nasty and will probably take fricking forever to run if you have a large db. Seems like there should be an easier way to do some kind of search, but I don't know.
Hmmm, maybe I'll try to write that query just for fun. Give me a few minutes.

 

[DT4K]

This works in MS SQL.
It looks for whatever string value you want in EVERY varchar, nvarchar, char, varchar, text, or ntext column in EVERY user table in the database.

After writing it, I realized it would probably run faster if you changed it to only build one query for each table. As it is now, it executes a select statement for each column. It could be changed to just do one select for each table and do "where colA = 'some value' or colB = 'some value' ", etc.

It's actually pretty cool though. It took about 4 minutes to run on our test DB that has about 200 tables and is about 3GB right now. If you change the like '%whatever%' to = 'whatever', it only takes about 2 minutes.

 

[kamper]

Just as an example of why you might possibly want to do this: I have worked on a system that uses GUIDs for keys on all records that must be unique. Every now and then while testing you get a guid but you're not sure what type of record it belongs to (since a GUID will never be duplicated anywhere, even in different tables except in foreign key situations) so it'd be handy to see if that key exists anywhere in the database. Of course, there was no need to do this programatically in the actual product, it was just to help me figure out what was going on. Something like DT4K's code looks pretty handy.

 

[Armitage]

Originally posted by: kamper
Just as an example of why you might possibly want to do this: I have worked on a system that uses GUIDs for keys on all records that must be unique. Every now and then while testing you get a guid but you're not sure what type of record it belongs to (since a GUID will never be duplicated anywhere, even in different tables except in foreign key situations) so it'd be handy to see if that key exists anywhere in the database. Of course, there was no need to do this programatically in the actual product, it was just to help me figure out what was going on.

It seems in this case that you'd be only searching one column from every table ... not every column from every table.

Something like DT4K's code looks pretty handy.

Not every slick solution answers a reasonable problem 😛

 

[kamper]

Originally posted by: Armitage
It seems in this case that you'd be only searching one column from every table ... not every column from every table.

Well, more than one column per table because I'd want to know about foreign key references. What it'd boil down to is isolating every column who's type is the guid (I think it's called UniqueIdentifier in sql server).

 

[DT4K]

Originally posted by: Armitage

Something like DT4K's code looks pretty handy.

Not every slick solution answers a reasonable problem 😛

LOL. I know what you mean. I just wrote that code for fun after this thread got me wondering how I would do something like that.

I can think of a reason I might want to use it though. Our plant has department numbers assigned to different areas. Last year, they decided to change the numbering scheme of the departments. So we had to update the db and change all the old dept numbers to the new ones. Being able to search the whole db for the value of the old dept number would be pretty handy. Especially since we have some tables that have comment fields in them. If there is a text comment stored that references an old dept number, it would be helpful to be able to find it and update it without having to check every table to make sure you found all the fields that could potentially have a dept number in them.