Tuesday, November 11, 2008

How To search entire database?

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

check out these link


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

http://it.toolbox.com/wiki/index.php/Get_Records_from_ANY_table_in_a_Database_Matching_Specified_Value

9 comments:

笨蛋 said...

IS VERY GOOD..............................

corey911 said...

Search sql server database data for text, numbers, dates, xml, etc. using SQL Locator. Search results can be replaced, removed, saved, and shared. sql search engine for Microsoft SQL Server 2008 and SQL Server 2005.

http://www.SQLLocator.com

Reviewer said...

Hi I know a good software for searching the whole SQL Database schema. It calls TM Field Finder. I think you'll be able to download trial version from their website.
http://www.targetmind.com/

your friend said...

good information..
http://letslearnssis.blogspot.com

Alex said...

This handy tool: Search string in SQL database searches all columns of all tables in a database for a string keyword. Simply, easy, and it's free.

Unknown said...

Search SQL server data for anything without writing queries. Search like Google

Unknown said...

www.SQLLocator.com

Unknown said...

Search SQL server data for anything without writing queries. Search like Google

Arul said...

Nice....