Search Entire Database To find extended ascii codes in sql
We have issues with extended ascii codes getting in our database (128-155)
Is there anyway to search the entire database and display the results of any of these characters that may be in there and where they are located within the tables and columns.
Hope that makes sense.
I have the script to search entire DB, but having trouble with opening line.
DECLARE @SearchStr nvarchar(100) SET @SearchStr != between char(32) and char(127)
I have this originally that works, but I need to extend the range I'm looking for.
SET @SearchStr = '|' + char(9) + '|' + char(10) + '|' + char(13)
It's very unclear what your data looks like, but this might help you to get started:
declare @TestData table (String nvarchar(100)) insert into @TestData select N'abc' insert into @TestData select N'def' insert into @TestData select char(128) insert into @TestData select char(155) declare @SearchPattern nvarchar(max) = N'%[' declare @i int = 128 while @i <= 155 begin set @SearchPattern += char(@i) set @i += 1 end set @SearchPattern += N']%' select @SearchPattern select String from @TestData where String like @SearchPattern
Of course you'll need to add some code to loop over every table and column that you want to query (see this question), and it's possible that this code will behave differently on different collations.