Works beautifully!  Thank you very much!  Now I'm looking for a "numeric" search sproc...

Greetings,

 Im in the need of one of this searches, and I found a SP quite similar to this one, which uses a temp table instead of a cursor, but the idea is more or less the same...

 Though, I have an issue (I know that I dont actually NEED to do it this way, but im asking if its posible, because I've looking many alternatives and couldnt work it out):

 I'd like to do something like:
SELECT * FROM MyTable WHERE MyID IN (EXEC SearchAllFields paramSearchString )

(Actually, I want to do some joins, thats why I dont want to use the SP instead of the Select)

Of course, I cant use a SP as a Table, so i thought about using a view. A view is not possible, because it cant receive parameters. So the alternative would be using a function that returns a table.

That'd work wonderful, except for the fact that you cant:
Use EXEC in a function
Use #Tables in a function
Insert into normal tables in a function

So I guess that I will just have to use a SP... 

 Any ideas are welcome, thanks in advance!

Hello Alex!

Did you find what you needed?
I'm asking because in the procedure posted I also needed look for the numeric values and then I understood that for this, just add the "int, numeric etc ..." in line of the second cursor:

AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'', ''int'', ''numeric'')'

I hope I have helped and until the next time.

Have a good week

Luciana.
Excuse my English, I'm still learning...

How about just "where COLLATION_NAME is not null" instead of the DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' clause?  Looks like this hits all the text fields, even is another type is added in the next release.

Brian - looks like that approach would work as well.  Thanks for the alternative.

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tips' 
AND COLLATION_NAME is not null  

Hi;

I need some help; I have a scrip that will find an specific character in a field on a table

See below:

SELECT *, HospitalID AS Expr1, MCD_Status AS Expr2

FROM SYS_Admissions

WHERE (HospitalID = 15) AND (MCD_Status = N'pac')and (AccountNumber like '%-%')

That works fine; now I need to come up with a script that will delete the (dash) from the

Account number's field on records that have one.

In other words in Account Number field, I have account number that looks like this: 12345654-2345

I need to delete the dash from it so that it reads like this: 123456542345

please let me know and thanks in advance.

sincerely

Luis V

@Luis - you could just use a simple REPLACE command to make the change you are requesting.

UPDATE SYS_Admissions SET AccountNumber = REPLACE(AccountNumber,'-','')

I would do this on a test table first just to check your work.

hi Greg Robidoux

it worked like a champ, thanks a millions

first I tested on a test table and it worked.

you da man!

thanks

Hi Greg,

Thank you for the very handy example and explanations. It works like a dream!

I now have a clean up job that requires me to find any 'CR' CHAR(13) values that have snuck into the data.

How would I modify the SP to look for CHAR(13) in the field values??

Thank you in advance

@Antony - try something like this:

sp_FindStringInTable  @stringToFind = '%' + CHAR(13) + '%', @schema = 'dbo', @table = 'TestTable'

Hi Greg,

Thank you for the quick responce.

I tried the sugestion, however i get the following error message:

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'."

The script I used is below; (Obvoiusly ommiting real DB and Table names to protect the innocent and all. . . )

use []
go
exec sp_FindStringInTable  @stringToFind = '%' + CHAR(13) + '%', @schema = 'dbo', @table = ''

The SP name is underlined with a red zigzag line and the context message says;

'Could not find stored procedure 'sp_FindStringInTable'.'

In the @StringToFind value, the 1st '+' is underlined with a red zigzag line and the context message says;

'Incorrect syntax near '+'.'

And finally . . .

In the @StringToFind value, the 13 from 'CHAR(13)' is underlined with a red zigzag line and the context message says;

'Incorrect syntax near '13'. Expecting '(' or SELECT.'

Please help.

Thank you in advance.

@Antony - try something like this:

declare @string varchar(20) = '%' + CHAR(13) + '%'
exec sp_FindStringInTable  @stringToFind = @string, @schema = 'dbo', @table = 'Table_2'

I created the stored procedure without error but when I attempt to use it, I get the following error...

"There was an error"

This is all of the information that I am given.

I am using SQL 2008 server

EXEC sp_FindStringInTable '%APRN%', 'dbo', 'table_name'

@Kevin - did you try to put a real table name in instead of 'table_name'?  If the SP doesn't find the object it will give that generic error message.

Thanks for responding Greg...Yes, I did use a real table name.

The actual query is...

EXEC sp_FindStringInTable '%APRN%', 'dbo', 'specialty_privileges'

Here is the code I copied and pasted:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_FindStringInTable]    Script Date: 01/16/2013 09:24:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)

BEGIN TRY
   SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE'
   SET @where = ''

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = ''' + @schema + '''
   AND TABLE_NAME = ''' + @table + '''
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

   EXEC (@cursor)

   OPEN col_cursor  
   FETCH NEXT FROM col_cursor INTO @columnName  

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       IF @where <> ''
           SET @where = @where + ' OR'

       SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + ''''
       FETCH NEXT FROM col_cursor INTO @columnName  
   END  

   CLOSE col_cursor  
   DEALLOCATE col_cursor

   SET @sqlCommand = @sqlCommand + @where
   --PRINT @sqlCommand
   EXEC (@sqlCommand)
END TRY
BEGIN CATCH
   PRINT 'There was an error'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor  
       DEALLOCATE col_cursor
   END
END CATCH

@Kevin - try to add this code to the CATCH section.  You can add this right after the BEGIN CATCH and before the PRINT 'There was an error"

SELECT ERROR_NUMBER() ,ERROR_SEVERITY() ,ERROR_STATE() ,ERROR_PROCEDURE() ,ERROR_LINE() ,ERROR_MESSAGE()

Maybe this will give you more insight as to what the issue is.

@Greg - the error is...

156    15    1    NULL    1    Incorrect syntax near the keyword 'GROUP'.

@Kevin - I just changed the code to handle this.  Can you try to recreate the function.  I added [  ] around the column names, schema and tablename.

@Greg - THANKS...That worked!

Looks great. I did a similar (non-sproc) script as well posted here: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Since i've added the @FullRowResult and the @SearchStrTableName it looks like we are overall doing the same thing.

Hello! 

I tried to create a stored procedure in heidi and using phpmyadmin and got erors trying to copy and paste this code. Where do you copy this code in order to make it work and do you have to add parameters yourself ? I just tried to copy it in the ROUTINE BODY in heidi but it shows this eror: USE IS NOT ALLOWED IN STORED PROCEDURES.

i copied this code: 

USE master 
GO 

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = '' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 

   EXEC (@cursor) 

   OPEN col_cursor    
   FETCH NEXT FROM col_cursor INTO @columnName    

   WHILE @@FETCH_STATUS = 0    
   BEGIN    
       IF @where <> '' 
           SET @where = @where + ' OR' 

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
       FETCH NEXT FROM col_cursor INTO @columnName    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   --PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.' 
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH