find certain words across all table columns??

Question

Post reply

find certain words across all table columns??

neotokyo

SSCertifiable

Points: 7239
More actions
October 26, 2007 at 8:29 am

#180704

i remember finding a script years ago that searched through all tables/columns looking
for whatever word you specify.
for example: if i'm looking for a value called: 'transisterXX' and i know it exists
in the database, but not sure under which table.column it exists, how would I go about
finding it?
any ideas?
_________________________

Viewing 3 posts - 1 through 2 (of 2 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Fulltext search is what you are looking for. You can find more on http://technet.microsoft.com/library/ms142545.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Lowell SSC Guru Points: 323518 More actions · Answer 2

this will KILL you if you are searching a database that has a table with millions of rows..

this works really well whens earching for data like you describe:

CREATE PROCEDURE UGLYSEARCH

-- EXEC UGLYSEARCH 'TEST'

@SEARCHSTRING VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(500),

@TABLENAME VARCHAR(60),

@COLUMNNAME VARCHAR(60)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

SELECT

SYSOBJECTS.NAME AS TBLNAME,

SYSCOLUMNS.NAME AS COLNAME,

TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE

INTO #FKFINDER

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

ORDER BY TBLNAME,COLNAME

DECLARE C1 CURSOR FOR

SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME

OPEN C1

FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS <> -1

BEGIN

--SET @sql = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''

SET @sql = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'

PRINT @sql

EXEC (@SQL)

FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

END

CLOSE C1

DEALLOCATE C1

SELECT * FROM #RESULTS

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!