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
asked
Search for a String value in 2 columns of all table in a database
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
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.
script.txt
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 .
@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.
sql.txt
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
regards
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((
select
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;
--go
Hope this helps.
Oleg
23 People are following this question.
