hi
i have come accross a better solution.....we can write a stored proc
and run it whenever required. it may take more time for really big
databases.
*******************************
the stored proc is written
******************************
CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128),
@SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) >
@TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr,
'''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS
'Outcome'
END
************************************************
the stored can be simply called like this
*************************************************
--To replace all occurences of 'ABC' with 'XYZ':
EXEC SearchAndReplace 'ABC', 'XYZ'
GO
na*****@hotmail.com (EtN) wrote in message news:<dd*************************@posting.google.c om>...
If that, what I think you problem is, is true, then i think you can
try the next:
Make a cursor loop trough the tables in de sysobjects and make in
every loop a text string that contains a update statement for the
current table and execute that string. That works only when every
table has the same column names ofcourse...
For SQL Server 2000 something like:
DECLARE @Table_name sysname
DECLARE @cmd1 varchar(1000)
DECLARE c_table CURSOR
FOR SELECT [Name], FROM sysobjects WHERE [Name] LIKE ('ARC%') ORDER BY
[Name]
OPEN c_table
FETCH NEXT FROM c_table INTO @Table_name
WHILE(@@FETCH_STATUS <> -1)
BEGIN
SELECT @cmd1 = 'UPDATE ' + @Table_name + ' SET columnname =
''value'''
EXEC (@cmd1)
FETCH NEXT FROM c_table INTO @Table_name
END
CLOSE c_table
DEALLOCATE c_table
ra*******@rediffmail.com (Rajesh Garg) wrote in message news:<14**************************@posting.google. com>... I have many tables and in those i require to change some data. Say
from ARCA to ARCAEX. I am sure that the string is unique in the sense
there will be no ARCAABC. So what do i do change by not manually
needing to search in each table and the whole database and still can
be sure that the changes have taken place. Please help
Regards,
Rajesh
