sql Clean Data

CREATE Procedure [dbo].[CleanData]
AS
Begin
	Declare @cmdExec nvarchar(2048)
	Declare MyCursor CURSOR FOR
	select Table_Name, Column_Name from INFORMATION_SCHEMA.COLUMNS where Data_TYpe in ('varchar','nvarchar','text','char')
	--select COLUMN_NAME from ..INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and Data_TYpe='varchar'

	Open MyCursor

	Declare @TableField varchar(2048)
	Declare @TableName varchar(2048)

	FETCH NEXT FROM MyCursor
	INTO @TableName, @TableField

	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Set @cmdExec ='Update ' + @TableName + ' set ' + @TableField + ' = Substring(' + isnull(@TableField,'') + ',1,charindex(''<scr'',' + @TableField + ')-1)'
		Set @cmdExec ='Update ' + @TableName + ' set [' + @TableField + '] = Substring(isnull([' + @TableField + '],''''),1,charindex(''<scr'',[' + @TableField + '])-1) Where charindex(''<scr'',[' + @TableField + ']) > 0'
		exec sp_executeSQL @cmdExec
		--print @cmdExec
		FETCH NEXT FROM MyCursor
		INTO @TableName, @TableField
	END
	CLOSE MyCursor
	DEALLOCATE MyCursor
End
Cleans Data within a database, for use after a security breach.

Updated: Saturday 9th October 2010, 08:35pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.