Can I find a word from any tables, all columns in a database?

  • Hi experts,

        I want to find a word "AMKDWAPP" from any tables, any columns in a database, is it possible to have a query to do it?   I need to find which columns contain this word as well.

    Please help.  Thank you.

  • Is the word in the column/table names or in the data?

    One way or the other you'll be joining SYSOBJECTS (tables) and SYSCOLUMNS (columns) and either doing the query directly against this or building dynamic SQL to execute.

    Unless once again MS has hidden another secret bell or whistle and failed to notify me by registered letter of the new feature...  (Bill's always doing that.)

     

  • Someone else may have a better solution but this works:

    this cursor based solution  finds columns that are of type VARCHAR, and then returns EVERYTHING that matches your input string

    this would not be good for production code, that's for sure; in a big database i had it took over 4 minutes to loop thru all the tables, and find all instances of the word 'TEST'

    CREATE PROCEDURE UGLYSEARCH

    -- EXEC UGLYSEARCH 'TEST'

    @SEARCHSTRING VARCHAR(50)

    AS

    SET NOCOUNT ON

    DECLARE @sql      VARCHAR(200),

    @TABLENAME        VARCHAR(30),

    @COLUMNNAME       VARCHAR(30)

    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)='VARCHAR'

        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 + '%'''

            PRINT @sql

            EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

        END

    CLOSE C1

    DEALLOCATE C1

    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