tiwaryrohit143 avatar image
tiwaryrohit143 asked

Search for a String value in 2 columns of all table in a database

Hi, I need a script where for all tables in a database for 2 columns which are of varchar type. I need to print those tables with columns having a particular record in it.

Thanks in advance

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lukasz8519 avatar image Lukasz8519 commented ·

could You send to us what you have done ?
i think you can write loop or cursor wher you check values in those tables, i think this is not very complicated

0 Likes 0 ·
tiwaryrohit143 avatar image tiwaryrohit143 Lukasz8519 commented ·

There is a column 'Insertby' Which is present in all tables. This column has value starting in this pattern s_**** . for some of the tables this column is getting different value which is wrong. So i need those lists of tables having '%s_% as values in this column. If i can get any script or query.

0 Likes 0 ·
tiwaryrohit143 avatar image tiwaryrohit143 commented ·

I have uploaded what i have done. This query is taking lot of time more than half an hour. and sometimes its automatically terminates.

Kindly if you can help me improving or a new script .

0 Likes 0 ·
script.txt (1.9 KiB)
Oleg avatar image Oleg tiwaryrohit143 commented ·

@tiwaryrohit143 The script in your comment is too complicated, there is no reason to use any loops and you somehow managed to use the nested loop. Also, by looking at the sample search criteria, it appears that you want to find the records which have the letter s as a part of the value and I am not sure whether this is intended. When you use the value of s_ as your search string and then make it a LIKE predicate, the underscore means a single occurrence of any character. Then you add the wild character to both ends, so now your search criteria means zero or more of any characters followed by letter s followed by one any character followed by zero or more of any characters. To put it simply, you want to find all values which have more than 1 character, out of these at least one is the letter s. If this is what you actually want then I guess it is OK, but it just looks weird. Also, why do you include the integer and decimal columns into consideration? It does not make much sense when you are searching for something clearly character based, not numeric. Finally, why do you say in 2 columns when in reality you consider all columns? Please clarify.

0 Likes 0 ·
Show more comments
Lukasz8519 avatar image
Lukasz8519 answered


i checked your code, add few lines, in my pc this code works fast, but i was wondering why you didn't retreive only distinct rows, you selecting all this is has no sense


sql.txt (2.3 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered

Here is the script which will search for the values in the specified column with the specified pattern in all tables. Before posting the script I need to elaborate a bit on the search pattern. Based on the information in question and comments, it appears that there is a fundamental misunderstanding of the wild characters in LIKE patterns. The underscore does indeed have a special meaning, it means exactly ONE (not more not less than one, but just one) occurrence of ANY character. So, in order to find the match for the value which itself includes the underscore, the underscore must be escaped. Here is the script which should produce desired results, I am not sure how useful it will be without more info from each record, but at least it does not use any loops:

declare @sql nvarchar(max);
declare @pattern varchar(100) = 's_';
select @sql = stuff((
    char(10) + 'union all' + char(10) + 'select ''' + [name] + 
    ''' TableName, Insertby ColumnValue from ' +
    quotename(schema_name([schema_id])) + '.' + quotename([name]) + 
    ' where Insertby like ''' + replace(@pattern, '_', '$_') + '%'' escape ''$'''
    from sys.objects
    where [type] = N'U'
    order by [name] for xml path('')), 1, 10, '');

-- uncomment to see how the resulting script looks like
--print @sql

exec sp_executesql @statement = @sql;

Hope this helps.


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.