Search in all the DB

Question

Post reply

Search in all the DB

tilew-948340

Hall of Fame

Points: 3431
More actions
September 12, 2010 at 6:28 am

#226807

Hi
How can I search a specific word in all the DB?
I have this DB with about 200 tables. Half of it has a "comments" column, but not all column has the same name. Would that help if I change the name of those "comments" column to the same name?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try the script at:

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 2

Well, this is more complicated than I was expected, but it is the solution!

Thank you very much!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You're welcome and glad to help.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 4

Dear Friends,

This is freeware and very easy to use ....

Please try this ...

http://www.red-gate.com/products/SQL_Search/index.htm?utm_source=cp&utm_medium=email&utm_term=3534&utm_content=search_launch&utm_campaign=sqlsearch

Varinder Sandhu,
http://www.varindersandhu.in/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Despite being from red-gate, I think I would prefer the tsql solution. Nothing to install and it works free of charge.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 6

Dear Friends,

i agree with you to use tsql solution. there is no problem to use ...

FYI : solution that i provided above is also free and it works free of charge. it is very easy to user because of GUI and can search any object across SQL Server ( if you want) you can search with in db is also

Thanks!!!

Varinder Sandhu,
http://www.varindersandhu.in/

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 7

From what I have seen, the reference is "SQL prompt v4.0" and it is free on trial basic e.g. 14 days.

Seems like a nice software, but I think it would be more for the users than for the admin/developer.

So, I'll go for the Tsql solution. But thank you anyway for the information because you don't know every thing from my situation and be sure that the more I know about SQL server and his tools, the more I would be efficient!

Thanks!

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 8

HI

agree !!!

don't use Ready made made Ur ready.

Ali
MCTS SQL Server2k8

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 9

if this is not fast enough your other option is to create a full text search catalog, add the needed tables and col....then do a search on contains....this will more then likely will return your results much faster....as this is more of a google type search

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

456789psw (9/21/2010)
if this is not fast enough your other option is to create a full text search catalog, add the needed tables and col....then do a search on contains....this will more then likely will return your results much faster....as this is more of a google type search

This only works if you know which columns to add.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

The SQL Search tool is in fact a really cool and free tool from redgate. It does not however accomplish what this person is trying to do. The SQL Search will find all references to a search string within the database schema, it will NOT however look at data which is what the original poster was trying to accomplish. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

SeanLange (9/22/2010)
The SQL Search tool is in fact a really cool and free tool from redgate. It does not however accomplish what this person is trying to do. The SQL Search will find all references to a search string within the database schema, it will NOT however look at data which is what the original poster was trying to accomplish. 😛

That is good info to know

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events