Searching - 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

SQL Server Development (2000)

Searching

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 09:16:15

I need to seach for something in my database that's not a table, column, view, procedure, function with a name... I'm looking for ANY column content in any table with any particular string. Can it be done, returning a <table>.<column> ?

Is it possible? In this instance I'm looking for an email address, so anything like '%@%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 09:21:56

You need to use dynamic sql along with system tables like syscolumns & sysobjects to achieve this. loop through each column of each object and search for the pattern. Can i ask why you're doing this?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 09:30:47

I'm doing it because I so often get lumbered with huge databases, badly designed, with terrible column names with just numbers for names and I won't make any progress finding out what columns are for what if they're not documented properly. I have to ask everyone where everything is - in this case which table & column are contractor's email adresses. To be able to find this out by using intelligent column content search would be quicker than interrogating lots of people who might not know the answer. If I could find out where emails are stored, this would be handy. Looking in a column is almost always easy - phone number - zipcode, but here, 90% of columns are empty.... and badly-named!

You seem to have an answer here. Can you fill me in on a method?

Thank you very much!

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 09:34:27

I'm used to

select * from sys.table
column
procedure

and so on for 2005

Now with 2000 it seems a little different, with

select * from sysobjects with a bit less detail...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-03 : 09:36:15

I wrote a documentation app that may help you out. It will show you how all the objects relate to one another, and also show you (optionally) the top N rows from each table and view. it generates an indexed chm so you can quickly search for any string and find all the objects where it it's referenced. see the link in my sig for details.

if you want to search the data in your tables for any string, this might help: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

elsasoft.org

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 09:59:19

Is there any way I can use that brilliant SearchAllTables search not for a particular string, but anything containing a part of a string, as if I was looking for like '%abc%'

or like 'abc?xy?z'

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 10:02:34

It could save me a lot of days. Not because I'd be hunting manually, but because someone would come in at the end of the week and just tell me where to find this stuff. But I'd rather do it myself and not be bored waiting.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-03 : 10:12:36

did you read the proc DDL? that proc is already using LIKE, so you'd just pass it 'abc' if you want to match '%abc%'. or for your other example, pass 'abc%xy%z'

note that the perf will be terrible. don't put this proc on any production server!

elsasoft.org

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 10:40:23

Your procedure has worked perfectly and I will keep it with me forever!

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 10:43:37

What if 'perf'? I copied the proc to all the databases I wanted to search, and kicked them all off, before your reply I just saw. I've been waiting for results...

All the databases are production.

What is perf?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-03 : 10:53:51

1. the proc is not mine. i didn't write it.

2. perf is performance. the performance of this proc is terrible because it's searching every character type column in every table using a where clause that can't use any indexes. so it's doing many, many, many table scans.

3. i would never run this proc on any production database because the perf is so bad. but then, when need to search for data, I know what column to look in. The only reason to use this proc is if you have no idea where the data is that you are looking for.

elsasoft.org

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 11:15:21

I recognised the writer of that. Well-known. Perfect answer to my question and you provided it, and I wouldn't have known where to get this.

I enjoyed watching it run on an 8-processor production machine and really tax it.

Now I've got a result, I'd like to refine that and eliminate a lot of rows and specify just one table & column. I won't mess with the proc yet but I thought I could do a WHERE on the output columns, using the procedure as the table in the FROM clause. I seem to be getting the syntax wrong...

select * from (exec searchalltables '@') where columnnane like '%Ccs_Company_File].[Ccs_Cd_Email%'

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'exec'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

What do you think?
Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-03 : 13:53:22

what you are trying to do is not valid t-sql. Do you have a DBA in your organization with more t-sql experience that you could talk to? I am a bit nervous about someone with your level of sql knowledge (beginner) running queries on production machines.

anyway, to do what you are attempting, you have to insert the procs result set into a table, then you can query it:

insert #mytable -- assumes you already created #mytable
exec searchalltables '@'
go

select top 10 * from #mytable
where columnnane like '%Ccs_Company_File].[Ccs_Cd_Email%'
go

elsasoft.org

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-04 : 04:22:34

Just tell me how to create a temp table...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 04:30:15

Temp tables are created just like normal tables:

CREATE TABLE #TEMP
(
col1 int,
col2 varchar(20),
...
)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-04 : 04:32:45

I get

Server: Msg 8164, Level 16, State 1, Procedure SearchAllTables, Line 54
An INSERT EXEC statement cannot be nested.

when I do

create table #sat (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

insert #sat
exec searchalltables '@'
go

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-04 : 04:55:41

The problem with nesting is that there's another EXEC running inside there at line 54. What the problem? Why can't it just do that? I did run it like that, anyway, originally!

OK all I'm trying to do is rather than just get this brilliant procedure's result into a result grid and have a little Google at it I want to stick the results into a table and do a WHERE on it to make things a little easier. Maybe GROUP on it too. That's all. When I just run the proc, all well. Just INSERTing to a table, the error is brought up about nesting EXECs. Can the expert tell me why?? Tell me Jez, Harsh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 07:57:06

quote:
Originally posted by mikebird

I recognised the writer of that. Well-known. Perfect answer to my question and you provided it, and I wouldn't have known where to get this.

I enjoyed watching it run on an 8-processor production machine and really tax it.

Now I've got a result, I'd like to refine that and eliminate a lot of rows and specify just one table & column. I won't mess with the proc yet but I thought I could do a WHERE on the output columns, using the procedure as the table in the FROM clause. I seem to be getting the syntax wrong...

select * from (exec searchalltables '@') where columnnane like '%Ccs_Company_File].[Ccs_Cd_Email%'

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'exec'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

What do you think?
Thanks

Method 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-04 : 08:38:18

Madhivanan

Great proposal. I went with option 2, as the first one with what I've been trying and couldn't see what the problem was with that. Can you tell me what the parameters should be in OPENROWSET please? Whatever I set as Data Source, it makes no difference and the SP I'm trying to run can't be found, although I've put this in a number of databases. Whether I try all existing databases as the data source or put anything in there, such as 'a' or 'x', or keep the bracketed remote server out - just used the same name as was in the drop-down in Query Analyzer... I get the same error...

Select *
from OPENROWSET('SQLOLEDB','Data Source=[ken-sql-002].constructmobile;Trusted_Connection=yes;
Integrated Security=SSPI','Execute searchalltables ''@''')

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'searchalltables'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 08:45:12

You need to prefix database name with proc:

Select *
from OPENROWSET('SQLOLEDB','Data Source=[ken-sql-002].constructmobile;Trusted_Connection=yes;
Integrated Security=SSPI','Execute db1..searchalltables ''@''')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-04 : 08:55:08

What you were saying there as db1.. was probably [ken-sql-002].constructmobile as the data source. Above, that is my server and database. Tell me what goes where?????

What the hell is OPENROWSET? Looks a bit like OPENQUERY and I never had any luck with that either

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources