find bad data

USE [Dev]
GO

/****** Object:  StoredProcedure [dbo].[FindBadData]    Script Date: 05/31/2016 16:49:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**********************************************************************************************************************
**
**    Procedure       : FindBadData
**    Purpose         : To locate a column within a given table that contains unsupported data, and output said data.
**    Author          : [redacted]
**    Date            : 06/05/2016
**    Version History : 06/05/2016   Initial version created to speed up finding crap
**
**********************************************************************************************************************/

/*
    exec dev.dbo.FindBadData 'temp_import', 'WorkEmail'
*/

ALTER PROCEDURE [dbo].[FindBadData]
    @TableName    nvarchar(255),   --the table to search for bad stuff
    @identifier   varchar(255)     --a column in the table that we can use to identify a row
AS

declare @MaxOrdinal        int
declare @CurrentOrdinal    int
declare @sSQL              varchar(5000)
declare @currentCol        nvarchar(max)
--We could probably declare an email variable so we can send a report straight out to that person.

--select @TableName = 'test_table'--SET THIS
--select @identifier = 'id' --This needs to be something that exists in the table that you can use to ID the messed up row,
--Note this is a varchar, so using a float will give you screwy results
select @CurrentOrdinal = 0
select @currentCol = 0
select @MaxOrdinal = 0 --the three '0's just ensure it will run if for some reason they don't get set further down.

IF OBJECT_ID ( @TableName , N'U') IS NOT NULL --verify the table exists
begin

create table #numbers
(
    number int
)
insert into #numbers values (1)
while (select max(number) from #numbers) < 1000
begin
    insert into #numbers
    select max(number) + 1 from #numbers
/*Create a numbers table, as i dont know if we have one in the db. We use this to loop through the substring of a column character by character
looking at the ASCII values of each character and assessing if the character is supported or not.*/
end

create table #BadData
(
    IdentifierRow            nvarchar(255),
    UnsupportedValueColumn   nvarchar(255),
    UnsupportedValue         nvarchar(max) --Short but sweet, a table to store and identify the bad data
)

--'select * from information_schema.columns where table_name = ''' + @TableName + ''' and Ordinal_position = ' + convert(varchar(5),@CurrentOrdinal) + '

select @MaxOrdinal = MAX(ordinal_position) from information_schema.columns where table_name = '' + @TableName + ''
--To get out loop counter we look at the schema of the table and find the max ordinal value (how many columns there are in the table)
--so we don't try to loop past that number

    while @CurrentOrdinal < @MaxOrdinal --start loopin'
    begin
        select @currentCol = Column_name from information_schema.columns where table_name = '' + @TableName + '' and Ordinal_position = @CurrentOrdinal
        --Get the current column name, so we can output if that column has unsupported values

        set @sSQL = --dynamic sql. seriously, its made of hate
        'SELECT
            CONVERT(varchar(255),'+@identifier+'),   ''[' + @currentCol + ']'' AS  [UnsupportedValueColumn],  [' + Convert(varchar(100),@currentCol) + ']  AS [UnsupportedValue]
            FROM ' + @TableName + ' y
                INNER JOIN #numbers n ON n.Number <= LEN( ['+Convert(varchar(100),@currentCol)+'] )
            WHERE ASCII(SUBSTRING(['+Convert(varchar(100),@currentCol)+'],  n.Number, 1))<32 OR ASCII(SUBSTRING(['+Convert(varchar(100),@currentCol)+'], n.Number, 1))>127

            OPTION (MAXRECURSION 1000)
            '
        /*
        Essentially we select the contents of the ID row, the name of the current column, and the contents of the current column that contains unsupported data.
        We do it in dynamic SQL because other wise we would only select either the contents or the name of the row.
        We join it on to the numbers table and ensure we know how many characters there are in to so we dont substring past that many.
        We also need to let the server know it can use recursion as many times as we have numbers (1000), this bypasses the default recursion which is lower
        The ASCII Values I look for are outside of teh standard alphabet, however some characters are accepted in the app, perhaps replacing the ASCII codes in the
        below statement would be good
                        OR ASCII(SUBSTRING(['+Convert(varchar(100),@currentCol)+'], n.Number, 1)) not in (
            129,130,131,132,133,136,137,138,139,140,142,144,147,148,149,150,151,152,153,160,161,162,
            )
        */

        --select @sSQL
        if (select DATA_TYPE from information_schema.columns where table_name = '' + @TableName + '' and Ordinal_position = @CurrentOrdinal )
        in ('varchar', 'nvarchar' ,'char' )
        --We insert the rows into our table, but only if the current column is a varchar, nvarchar, or char as we know that others won't contain unsupported ASCII
        begin
            insert into #BadData
            exec (@sSQL) --Simple. Our dynamic SQL is a select statement, so we can insert with that statement
        end

        select @CurrentOrdinal = @CurrentOrdinal + 1 --loop stuff
    end
select distinct * from #BadData -- display all the "unsupported" data
drop table #numbers
end
else
begin
print ('The table ' + DB_NAME() + '.dbo.' + @TableName + ' does not exist')
end

--delete d
--from temp_import d
--join #BadData b
--on b.identifierRow = d.workemail
----If we wanted we could simply delete the crap from the import and continue, then use the #BadData table as a report.

--commit