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
