Sqlpro search for coulmn9/25/2023 ![]() Yes I have tried for different tables, same issue. Is there anyway to modify this so that for each record returned it can, in some way, indicate all the matches? Like list each column for each record to look in or highlight the matches in the results? wouldn't it?ĬREATE PROCEDURE dbo.sp_FindStringInTable_with_flag VARCHAR(100), sysname, sysname Why exactly do we keep 2005 compatibility ?Ĭross apply ',') would work on any server you have there. For the comment about receiving several tables using XML.WHERE upper(column_name) COLLATE Latin1_General_100_BIN2 LIKE this SP just replace – We can easily search more efficient using the collation. I use the folling code! Has anyone an idea to search not only for one "stringtoFind" but to use In(Select Searchstring from table)ĬREATE PROCEDURE sp_FindStringInTable VARCHAR(100), sysname, sysname SELECT = + ' LIKE ''' + + ''' OR 'Īfter the stored procedure has been created it needs to be marked as a system ![]() WHERE TABLE_SCHEMA = TABLE_NAME = DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') SELECT = + 'case when LIKE ''' + + ''' then 1 else 0 end as ' + COLUMN_NAME + '_found, ' This database setting can be checked using theĬREATE PROCEDURE dbo.sp_FindStringInTable_with_flag VARCHAR(max), sysname, sysname Setting had cursors set to local, so the script above did not work because it needed We recently ran into an issue that I worked on with Russ Mittler where the database This is a newer and simpler approach and eliminates the need for a cursor. Option 2 - Does Not Use a Cursor (new method) IF CURSOR_STATUS('variable', 'col_cursor') -3 SET = + ' LIKE ''' + + ''''įETCH NEXT FROM col_cursor INTO col_cursor SET = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAMEįROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNSĪND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' CREATE PROCEDURE dbo.sp_FindStringInTable VARCHAR(100), sysname, sysname
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |