find and replace - show table and column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

find and replace - show table and column

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-06-25 : 23:58:45

This query is very useful. But I want to change this query and display more information include display the text in which table and column fields. for example :(852) have found in 5 table and list 5 tables name and column fields.

This change query purpose is check the data is store on right table with column fields and let us can make decision is it go to replace data or not.

SET NOCOUNT ON

DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT

SET @stringToFind = '(852)'
SET @stringToReplace = '+852'

DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1

OPEN TAB_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

OPEN COL_CURSOR

FETCH NEXT FROM COL_CURSOR
INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName
+ '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),'''
+ @stringToFind + ''',''' + @stringToReplace + ''')'

SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''

EXEC( @sqlCommand + @where)

SET @count = @@ROWCOUNT

IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END

FETCH NEXT FROM COL_CURSOR
INTO @columnName
END

CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END

CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources