>>Hi everybody,
>>
>>With this select
>>select Table_Name, Column_Name, Data_Type, Character_Maximum_Length from information_schema.columns where Character_Maximum_Length >=100 order by 1,2
>>
>>I can get a list of all potentially dangerous fields. Now I want to write a procedure to generate a script to change all of these fields to varchar(100) (not execute it, of course).
>>
>>How should I proceed in writting such code?
>>
>>Thanks in advance.
>
>

>DECLARE @Test varchar(max)
>SET @Test = NULL
>select @Test = ISNULL(@Test,'')+'ALTER TABLE '+Table_Name+' ALTER COLUMN '+Column_Name+' '+Data_Type+'(100)'+CHAR(13)+CHAR(10) 
>       from information_schema.columns where Character_Maximum_Length >=100 order by Table_Name,Column_Name
>print @Test
>

>NOT TESTED

Interesting, thanks :)

Anyway, I figured much better way yesterday. I wrote the script in home using VFP and textmerge. Here are my programs

IF NOT USED('LongFields')
	USE LongFields SHARED IN 0
ENDIF
SELECT LongFields

LOCAL lcTable
lcTable = ''
SET TEXTMERGE ON
SET TEXTMERGE TO MEMVAR SQLScript NOSHOW
TEXT
    DECLARE @ErrorSeverity INT,
            @ErrorNumber   INT,
            @ErrorMessage nvarchar(4000),
            @ErrorState INT,
            @ErrorLine  INT,
            @ErrorProc nvarchar(200)
ENDTEXT

SCAN
	
		IF NOT m.lcTable  == ALLTRIM(Table_Name)
			lcTable = ALLTRIM(Table_Name)
		ENDIF

		IF INLIST(LOWER(field_type),'nvarchar','varchar')
		 \
         \PRINT 'Changing table <<m.lcTable>>..........'
		 \ BEGIN TRY
         \    ALTER TABLE [<<m.lcTable>>] ALTER COLUMN [<<ALLTRIM(Field_Name)>>] varchar (100)
         \    PRINT 'Table <<STRTRAN(m.lcTable,"'","''")>> column <<STRTRAN(ALLTRIM(Field_Name),"'","''")>> is successfully changed.' 
         \ END TRY
         TEXT
            BEGIN CATCH
                                -- Grab error information from SQL functions
                                SET @ErrorSeverity = ISNULL(ERROR_SEVERITY(),0)
                                SET @ErrorNumber   = ISNULL(ERROR_NUMBER(),0)
                                SET @ErrorMessage  = ISNULL(ERROR_MESSAGE(),'')
                                SET @ErrorState    = ISNULL(ERROR_STATE(),0)
                                SET @ErrorLine     = ISNULL(ERROR_LINE(),0)
                                SET @ErrorProc     = ISNULL(ERROR_PROCEDURE(),'')
                                SET @ErrorMessage  = 'SQL Server Error Message is: ' + 
                                CAST(@ErrorNumber AS VARCHAR(10)) + ' in procedure: ' + 
                                @ErrorProc + ' Line: ' + 
                                CAST(@ErrorLine AS VARCHAR(10)) + ' Error text: ' + @ErrorMessage
                                
                                -- Not all errors generate an error state, to set to 1 if it's zero
                                IF @ErrorState  = 0
                                SET @ErrorState = 1
                                -- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
                                IF @@TRANCOUNT > 0
                                BEGIN
                                        --print 'Rollback transaction'
                                        ROLLBACK TRANSACTION
                                END
                                --RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)
                                PRINT @ErrorMessage
                        END CATCH
                        ENDTEXT
** Skip all other cases (image, text, ntext)
		ENDIF
	
ENDSCAN
SET TEXTMERGE OFF
SET TEXTMERGE TO
_CLIPTEXT = SQLScript

And to test for malicious script

IF NOT USED('LongFields')
	USE LongFields SHARED IN 0
ENDIF
SELECT LongFields

LOCAL lcTable
lcTable = ''
SET TEXTMERGE ON
SET TEXTMERGE TO MEMVAR SQLScript NOSHOW

SCAN
	
		IF NOT m.lcTable  == ALLTRIM(Table_Name)
			lcTable = ALLTRIM(Table_Name)
		ENDIF

		IF INLIST(LOWER(field_type),'nvarchar','varchar')
		 \
         \PRINT 'Testing table <<m.lcTable>>..........'
		 \ IF EXISTS (SELECT 1 from [<<m.lcTable>>] WHERE [<<ALLTRIM(Field_Name)>>] LIKE '%<script>%')
		 \      BEGIN 
		 \
		 \          PRINT 'We have malicious script in table  <<STRTRAN(m.lcTable,"'","''")>> in column <<STRTRAN(ALLTRIM(Field_Name),"'","''")>> '
		 \          PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
		 \        SELECT [<<ALLTRIM(Field_Name)>>] from [<<m.lcTable>>] WHERE [<<ALLTRIM(Field_Name)>>] LIKE '%<script>%'
		 \       RAISERROR('Error in table  <<STRTRAN(m.lcTable,"'","''")>> in column <<STRTRAN(ALLTRIM(Field_Name),"'","''")>> ',16,1)
		 \       return -1   
		 \       --- uncomment line below and insert the actual text (script) to replace
		 \       --- UPDATE [<<m.lcTable>>] SET [<<ALLTRIM(Field_Name)>>]  = REPLACE([<<ALLTRIM(Field_Name)>>],'Malicious script','')    
		 \      END  
          ** ignore all other cases (image, text, ntext)
		ENDIF
	
ENDSCAN
SET TEXTMERGE OFF
SET TEXTMERGE TO
_CLIPTEXT = SQLScript