-4

I have a database with 100 tables. how can i know which table contain a particular text.

For example which table contains text 'Assumed Life Claims'

I can give a hint that this filed is a varchar

SQL SERVER 2008

John Doyle
  • 7,475
  • 5
  • 33
  • 40
Kuttan Sujith
  • 7,889
  • 18
  • 64
  • 95
  • 1
    SQL-Server, MySQL, Postgres, Oracle, SQLite, ...? Which DBMS? – ypercubeᵀᴹ Feb 11 '12 at 17:07
  • I know about CONTAINSTABLE in mssql could be other fulltext search functions in other DBs – Sergey Benner Feb 11 '12 at 17:08
  • Using Dymanic SQL, find all tables that have varchar columns (and the columns names) and then search those columns. See here for how to use `INFORMATION_SCHEMA` for part one: [SQL Query to search schema of all tables](http://stackoverflow.com/questions/1266960/sql-query-to-search-schema-of-all-tables) – ypercubeᵀᴹ Feb 11 '12 at 17:10

1 Answers1

0

ypercube's suggestion is exactly correct. Here is an implementation:

DECLARE @searchText VARCHAR(100)
SET @searchText = 'Assumed Life Claims'

DECLARE @sqlText VARCHAR(8000)
DECLARE @MaxId INT
DECLARE @CurId INT
DECLARE @possibleColumns TABLE (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
                               ,sqlText VARCHAR(8000))
INSERT INTO @possibleColumns(sqlText)
SELECT 'IF EXISTS (SELECT * FROM ' + c.TABLE_NAME + ' WHERE ' + c.COLUMN_NAME + ' = ''' + @searchText + ''') PRINT '' searchText=' + @searchText + ' was found in ' + c.TABLE_NAME + '.' + c.COLUMN_NAME + ''''
  FROM INFORMATION_SCHEMA.COLUMNS c
    -- Using hint that this field is a varchar
 WHERE c.DATA_TYPE = 'varchar'

SELECT @CurId = MIN(pc.Id)
       ,@MaxId = MAX(pc.Id)
  FROM @possibleColumns pc

WHILE (@CurId <= @MaxId)
BEGIN
    SELECT @sqlText = pc.sqlText
      FROM @possibleColumns pc
     WHERE pc.Id = @CurId

    -- For testing (uncomment)
    --PRINT @sqlText

    EXEC(@sqlText)

    -- Increment counter
    SET @CurId = @CurId + 1
END
Steven Schroeder
  • 5,904
  • 2
  • 21
  • 15
  • Thank you..... http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm thank you all – Kuttan Sujith Feb 11 '12 at 18:02
  • i got this link from http://beyondrelational.com/blogs/naomi/archive/2010/10/29/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx – Kuttan Sujith Feb 11 '12 at 18:04
  • @user444569 I see that the links you pasted also help to answer your question but I feel that my answer is complete and I have invested time to test it and believe it to be error free. If it is not, I would appreciate if you could share the details on the error you mentioned so that I can help to refine the answer I provided. Thank you. – Steven Schroeder Feb 11 '12 at 23:44
  • SELECT * FROM INFORMATION_SCHEMA.columns WHERE Column_Name like '%'+@SearchStr+'%' searches table column names – Kuttan Sujith Feb 15 '12 at 17:00