Database level change

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,
RajeshIf 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@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.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|||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'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) >
@.TableName
ANDOBJECTPROPERTY(
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)
ANDTABLE_NAME= PARSENAME(@.TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(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@.hotmail.com (EtN) wrote in message news:<ddaf11e.0308050435.21c5971a@.posting.google.com>...
> 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@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.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