Monday, January 25, 2010

Find and Replace text in Datatable


SET NOCOUNT ON
DECLARE @FindName VARCHAR(MAX) = 'AAA' --- <- Find Value
DECLARE @ReplaceName VARCHAR(MAX) = 'BBB' --- <- Replace Value

DECLARE @Table TABLE
(
ID INT IDENTITY(1,1)
, TableName VARCHAR(100)
, ColumnName VARCHAR(100)
)
INSERT INTO @Table
SELECT
tableSchema.TABLE_SCHEMA + '.' + SysObjects.name
, SysColumns.Name
FROM sysobjects
INNER JOIN SysColumns ON SysColumns.id = sysobjects .id
AND SysColumns.xtype IN ( 167, 175, 239, 231, 35, 99 )
INNER JOIN information_schema.tables AS tableSchema ON tableSchema.TABLE_NAME = SysObjects.name
WHERE sysobjects.xtype='U'
ORDER BY sysobjects.Name, SysColumns.Name

DECLARE @NoOfRecord INT = 0
DECLARE @CurrentRecord INT = 1
DECLARE @TableName VARCHAR(100) = ''
DECLARE @ColumnName VARCHAR(100) = ''
DECLARE @SQLCommand VARCHAR(MAX) = ''

SELECT @NoOfRecord = MAX(ID)
FROM @Table

WHILE (@CurrentRecord <= @NoOfRecord )
BEGIN
SELECT
@TableName = TableName
, @ColumnName = ColumnName
FROM @Table
WHERE ID = @CurrentRecord

SET @SQLCommand = 'IF EXISTS(SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindName + '%'')' +
' BEGIN ' +
' PRINT ''UPDATE ' + @TableName +
' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''''' + @FindName + ''''', ''''' + @ReplaceName + ''''') ''' +
' END '

-- PRINT @SQLCommand
EXECUTE(@SQLCommand)
SET @CurrentRecord = @CurrentRecord + 1
END

No comments: