data string in all the tables in a database.

• sql_2005_fan

  SSCommitted

  Points: 1864

  More actions

  January 13, 2009 at 11:27 am

  #205093

  How can I find out the specific data is existing in which database table.

  I am looking for all the the tables that has the string verizon.

  Is that possible?

  Thanks.

• FFalcon1961

  SSC-Addicted

  Points: 481

  More actions

  January 13, 2009 at 12:08 pm

  #926156

  Red Gate has in thier Tool Box a program calld DOC 2.0 you can download the trial. I would recommend buying it.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  January 13, 2009 at 12:23 pm

  #926169

  I'd tripped over the same issue previously.

  ok, really you only need to search columns that are of type varchar,char,nvarchar and ntext....you obviously wouldn't need to search a numeric field for a string.

  here's a handy procedure...but BEWARE...it can bring your server to it's knees! NEVER run this on production...make a copy, and punish your development server instead.

  here's why it's bad: if you have a table with a million/billion rows, and that table has 20 varchar/char columns, it will search the million/billion row table 20 times...once for each column.

  and every one of them will be a TABLE SCAN because it won't be using indexes for a LIKE statement.

  [font="Courier New"]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 = '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[/font]

  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!