Post reply

find certain words across all table columns??

  • 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?

    _________________________

  • October 26, 2007 at 8:35 am

    #745757

    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

  • October 26, 2007 at 9:52 am

    #745798

    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!

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

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