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

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


raj_chins (AT) rediffmail (DOT) com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5 (AT) posting (DOT) google.com>...

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

napel25 (AT) hotmail (DOT) com (EtN) wrote in message news:<ddaf11e.0308050435.21c5971a (AT) posting (DOT) google.com>...