Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unhappy Unanswered: find text string in database

    Hello,
    I'd like to find a specific text string searching in all tables within same database.
    Is there a way to make only one query to all tables at the same time?

    Thank you very much for your attention.

    Qslx

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. You would have to write a procedure that looped through all the tables and checked every column.

    This question makes me suspect that there are some design issues with your database schema.

    blindman

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya mean like:

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
    GO
    
    SET NOCOUNT ON
    
    DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int
    
    SELECT @Sargable = 'Beer'
    
    DECLARE insaneCursor CURSOR FOR 
    	SELECT c.TABLE_NAME, c.COLUMN_NAME 
    	  FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
    	    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
    	 WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')
    	   AND t.TABLE_TYPE = 'BASE TABLE'
    
    OPEN insaneCursor
     
    FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
    
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
    				+ '''' +  @TABLE_NAME + '''' + ','
    				+ '''' + @COLUMN_NAME + '''' + ','
    				+ 'COUNT(*) FROM [' + @TABLE_NAME  
    				+ '] WHERE [' + @COLUMN_NAME + '] Like '
    				+ ''''+ '%' + @Sargable + '%' + ''''
    		--SELECT @SQL
    		EXEC(@SQL)
    		IF @@ERROR <> 0 
    			BEGIN 
    				SELECT @SQL
    				SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
    				GOTO Error
    			END 
    		FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
    	END
    
    SELECT * FROM myTable99 WHERE Occurs <> 0 
    
    
    Error:
    CLOSE insaneCursor
    DEALLOCATE insaneCursor
    
    GO
    
    DROP TABLE myTable99
    GO
    
    SET NOCOUNT OFF
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2003
    Posts
    2

    Talking find text string in database

    Hi Brett,
    Thanks a lot for you help.
    It works great!

    Cheers,

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    brett, don't you have any hobbies?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...SQL

    That was a cut and paste from my toolbox....

    You kidding...I'm writting a sql server version of the window explorer find function...using xp_cmdshell, because it's tooooooooooooo painful to deal with server ops...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't tell when you're kidding and when you're not!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd say I'm an Enigma...but that's taken already...8-)

    And I finally got server ops to give me clearence, so I won't have to build the explorer

    Are Margarittas a hobby?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2011
    Posts
    1

    Genius

    I just registered to say thanks for this script.

    I use it all the time to find references in systems... its mega

  10. #10
    Join Date
    Feb 2013
    Posts
    1
    I've used this script many times now and it allways works. It is limited to specific data types though, but this can be extended by adding the datatypes to the line:
    Code:
    WHERE c.DATA_TYPE IN 'char','nchar','varchar','nvarchar','text','ntext')
    like so:
    Code:
    WHERE c.DATA_TYPE IN 'char','nchar','varchar','nvarchar','text','ntext','tinyint','int','smallint','bigint')
    Thank you so much Brett Kaiser!!

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sadly, Brett passed away a short time ago.
    But there is something absolutely cool about the fact that he is still helping people solve problems....
    R.I.P. Brett.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Sep 2013
    Posts
    1
    I registered to say thanks as well. It is very sad that Brett passed. Even in spirit, he sure saved my butt at work today.

  13. #13
    Join Date
    May 2014
    Posts
    1

    Help

    Hi, this script, i don't know why... don't work for me...

    i set my own database, but i receive this messagge...

    --

    the name of the column '?JobID' is not valid

    Il nome di colonna '?JobID' non valido.

    --

    can u help me?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Probably has something to do with the foreign language character set.

    Do you see in Brett's script where he had this?:

    Code:
    		--SELECT @SQL
    		EXEC(@SQL)
    Change it to this:

    Code:
    		SELECT @SQL
    		--EXEC(@SQL)
    Now the code will print out, rather than execute. You can then run the individual statements to see which column it errors out on, and why.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I bet you need to add square brackets around your identifier names.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •