Search DB for Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Search DB for Value

Author

Topic

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-17 : 12:42:58

I have an issue for the board.

I need a way to search the database (or just a table) for a specific value. I don't know what the field name is or where that value might show up, but I know the value. Is there any way to search the DB or a table for a value if you have no idea where the value might appear.

Psuedo code is:

select * from DB where fieldValue='12345678.22'

I'd like to see the whole record returned so I could see the field name where the value appears.

I know this is a wierd one. It should be in the schema or other documentation but it's not and the system is HUGE.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 12:49:17


SELECT 'SELECT * FROM ' + Table_Name + ' WHERE ' + Column_Name + ' = ''12345678.22'''
FROM INFORMATION_SCHEMA.COLUMNS

The above code is going to give you errors on some columns, such as INT, BIGINT, etc...

Tara

Edited by - tduggan on 06/17/2003 12:59:26

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 12:54:35

You might want to use the NOLOCK hint for the SELECTs because these queries could negatively affect performance.

SELECT 'SELECT * FROM ' + Table_Name + ' (NOLOCK) WHERE ' + Column_Name + ' = ''12345678.22'''
FROM INFORMATION_SCHEMA.COLUMNS

Tara

Edited by - tduggan on 06/17/2003 13:00:14

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-17 : 13:10:33

Wow, that was quick! Very interesting query. It seems to work okay, but I have some questions about what the results are showing me.

I got back 3535 lines in the result set which all read something like:

"SELECT * FROM v_PARTICIPATION WHERE PNOTE = '6150.00'"

However, if I run that result line as a stand-alone query, it returns nothing, which makes sense to me. There should be no PNOTE values in that table equal to 6150.00. So, I'nm trying to figure out what the results indicate to me. Can you explain?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 13:15:07

You need to copy the output of the below queries into a new window. Then run the queries in this new window. The queries are just doing exactly what you asked. You wanted a query for each column in each table that searched on the value that you provided. When you run the output and you get any records returned back, then you have the record(s) that you are looking for. I would not just run all of them though at one time. I would grab a batch at a time (you'll need to determine which ones to select depending upon the size of the tables) so that you don't completely affect production.

Tara

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-06-17 : 13:22:02

You can check out this link - http://vyaskn.tripod.com/sql_server_search_and_replace.htm. It has a stored procedure that does a search and replace across the entire database, but it can be modified to only search the database.

kramerd1506
Starting Member

8 Posts

Posted - 2003-06-17 : 13:28:44

Tara,

Oh! I see. I was expecting that the results would only show select statements for tables where the value appeared. That's what confused me. I got it now.

I'll try the stored procedure link too.

Thanks to both!

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-17 : 17:16:46

"I need to find my way to a place I can't remeber, but now I'd like to go home, but I don't know how I got here in the first place..."

There's GOT to be a better way...what are you trying to do?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources