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  General SQL Server Forums  Script Library  Want to Search the Whole Dang Database?

Author  Topic  tdickson Starting Member 1 Post Posted - 2004-10-06 : 22:01:38 Hope I'm posting this the right way (newbie forum member)... So, today I get this crazy request from a data migration team to search an ENTIRE database for a particular string value. The question goes something like this: "Hey, what's the syntax for searching the whole database for a string value? There are lots of tables and we know it's in one of them somewhere...Will 'SELECT * FROM *' work maybe?" You know, the really technical manager is asking. So I patiently explained to him that there really isn't a syntax in T-SQL directly for doing that and kinda walk out of the office for the day (they always ask you stuff like this at 5:08 pm). While reading some excellent stuff on SQLTeam.com, particularly an article by graz (http://www.sqlteam.com/item.asp?ItemID=1283) on "Letting SQL Server Write SQL Statements" I was inspired to take the two-step approach to this problem. I'll bet some smarter person can make this into a one-stepper, but I have a quick solution that doesn't require the purchase of any fancy database management utility and will hopefully make my manager a happy person in the morning. I should mention (for anyone that missed this) this is not exactly the most efficient use of a database server and should be implemented in situations where there is only a little data in each table or you're the only user. Here's the approach: 1. Use the INFORMATION_SCHEMA to get a list of all columns in all tables in your database that are CHAR, NCHAR, VARCHAR, or NVARCHAR types (sorry, TEXT and NTEXT won't work here). 2. In that same SELECT statement, use the COLUMN_NAME and TABLE_NAME to build a list of SELECT statements which will each query one column in the tables for the desired value. 3. Copy the results of your SELECT statement (text results, not grid) and paste them into a new Query Analyzer window. You can add a SET NOCOUNT ON at the top to reduce the output. 4. Once the series of generated statements executes, you'll have a line for each column in each table and the result of the individual table/column query. It's easy to scan for a YES! in the result (you could also modify the following to output nothing instead of NO). Soo, you use: DECLARE @SearchString VARCHAR(100) DECLARE @Comparison VARCHAR(4) -- Set this to whatever you want SET @SearchString = 'Hanna Moos' -- Set this to '=' or 'LIKE' SET @Comparison = '=' SELECT 'SELECT (CASE WHEN (SELECT COUNT([' + c.COLUMN_NAME + ']) FROM [' + t.TABLE_NAME + '] WHERE [' + c.COLUMN_NAME + '] ' + @Comparison + ' ''' + @SearchString + ''') > 0 THEN ''[' + t.TABLE_NAME + '].[' + c.COLUMN_NAME + '] - YES!'' ELSE ''[' + t.TABLE_NAME + '].[' + c.COLUMN_NAME + '] - NO'' END)' FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_TYPE = 'BASE TABLE' AND c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') to produce lines like this (the following is only a few columns from Northwind's Customer table): SELECT (CASE WHEN (SELECT COUNT([CustomerID]) FROM [Customers] WHERE [CustomerID] = 'Hanna Moos') > 0 THEN '[Customers].[CustomerID] - YES!' ELSE '[Customers].[CustomerID] - NO' END) SELECT (CASE WHEN (SELECT COUNT([CompanyName]) FROM [Customers] WHERE [CompanyName] = 'Hanna Moos') > 0 THEN '[Customers].[CompanyName] - YES!' ELSE '[Customers].[CompanyName] - NO' END) SELECT (CASE WHEN (SELECT COUNT([ContactName]) FROM [Customers] WHERE [ContactName] = 'Hanna Moos') > 0 THEN '[Customers].[ContactName] - YES!' ELSE '[Customers].[ContactName] - NO' END) which produce output like this: ------------------------------- [Customers].[CustomerID] - NO -------------------------------- [Customers].[CompanyName] - NO -------------------------------- [Customers].[ContactName] - YES! Hope this helps someone, it blew an hour of a perfectly good evening at home for me. Cheers!