Daniel, Included below is a stored procedure that you can use to search any or all columns in all tables. Once you have installed the SP, call it as follows: exec spDBSearch @value='MySearchString'
You can also specify a specific column, table or datatype. Let me know if you have any questions. ALTER PROCEDURE sp_DBSearch (@value varchar(255),@col varchar(255) = '',@table varchar(1024) = '', @datatype varchar(20) = '', @debug int = 0) as SET NOCOUNT ON -- cursor vars DECLARE @colName varchar(255),@tableName varchar(255),@dType varchar(50),@op varchar(10), @searchVal varchar(255),@query varchar(1024),@Where varchar(1024),@From varchar(255), @recordCount int SELECT @op = '=', @recordCount = 0 CREATE TABLE #x (RecordCount int) DECLARE cur CURSOR FAST_FORWARD FOR SELECT c.name as colName --,c.id ,o.name as tableName --,o.type ,t.name as "DataType" FROM sysColumns as c left outer join sysObjects as o on (o.id = c.id) left outer join sysTypes as t on c.xtype = t.xtype WHERE ( c.Name like IsNull(nullIf(@Col,''),c.name) ) and o.name like IsNull(nullIf(@table,''),o.name) AND t.name like IsNull(nullIf(@datatype,''),t.name) AND o.type = 'u' order by o.name,c.Name OPEN cur WHILE (1=1) BEGIN FETCH NEXT FROM cur INTO @colName,@tableName,@dType IF (@@FETCH_STATUS <> 0) BREAK SET @searchVal = @value if (CHARINDEX('%',@value) > 0) SELECT @op = 'LIKE', @searchVal = '''' + @value + '''' ELSE IF (CHARINDEX(',',@value) > 0) SELECT @op = 'IN', @searchval = '(' + @value + ')' ELSE SELECT @op = '=', @searchVal = '''' + @value + '''' SELECT @From = ' FROM ' + @tableName, @Where = ' WHERE ' + @colName + ' ' + @op + ' ' + @searchval SELECT @query = 'SELECT count(*) as RecordCount' + @from + @where INSERT INTO #x exec (@query) SELECT @recordCount = (SELECT top 1 recordCount FROM #x) truncate table #x if (@debug > 0) print('Searching ' + @tableName + '.' + @colName + ' for ' + @value + ' returned ' + cast(@recordCount as varchar(10)) + ' records') if (@recordCount > 0) BEGIN SELECT @query = 'SELECT ''' + @tableName + ''' as searchTable,''' + @colName + ''' as searchCol,*' + @from + @where --SELECT @query = 'SELECT ''' + @tableName + ''' as searchTable,''' + @colName + ''' as searchCol,* FROM ' + @tableName + ' WHERE ' + @colName + ' ' + @op + ' ' + @searchval exec (@query) END END CLOSE cur DEALLOCATE cur DROP TABLE #x >>> [EMAIL PROTECTED] 07/05/05 09:25AM >>> I have a table that has about 50 columns. I want to be able to search ALL columns for the string "***". I can loop through the columnList with a set of LIKE commands, but is there a way to search all columns without making 50 LIKE commands? -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211187 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54