Update all tables in database

[Muzamil]

Hi

My database contains , lets say, 100 tables. Out of these 100 tables,
60 tables contain both columns; Column1 and Column2.

I want to:

Update (All tables with BOTH of these columns)

Set Column1 = Column2

Is there a better way of doing it? OR do I have to do it manually for
each table.

Thanks for your help.

[Simon Hayes]

"Muzamil" <mu*****@hotmail.com> wrote in message
news:5a**************************@posting.google.c om...

Hi

My database contains , lets say, 100 tables. Out of these 100 tables,
60 tables contain both columns; Column1 and Column2.

I want to:

Update (All tables with BOTH of these columns)

Set Column1 = Column2

Is there a better way of doing it? OR do I have to do it manually for
each table.

Thanks for your help.

If this is a one-time task, then you can use a query like this, then copy
and paste the result set (assuming all your tables are owned by dbo):

select 'update dbo.' + t.TABLE_NAME + ' set col1 = col2'
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c1
on T.TABLE_NAME = C1.TABLE_NAME
join INFORMATION_SCHEMA.COLUMNS c2
on t.TABLE_NAME = c2.TABLE_NAME
where t.TABLE_TYPE = 'BASE TABLE'
and c1.COLUMN_NAME = 'col1'
and c2.COLUMN_NAME = 'col2'

If you need to do this more often, then you can declare a cursor for the
same query, and loop through using EXEC() to execute the commands
dynamically.

Simon

[Ross Presser]

On 26 May 2004 08:10:22 -0700, Muzamil wrote:

Hi

My database contains , lets say, 100 tables. Out of these 100 tables,
60 tables contain both columns; Column1 and Column2.

I want to:

Update (All tables with BOTH of these columns)

Set Column1 = Column2

Is there a better way of doing it? OR do I have to do it manually for
each table.

Thanks for your help.

If you can compile a table with the table names

CREATE TABLE TNames (TName varchar(32) primary key);

, then

SELECT 'UPDATE ' + TNames.TName + ' SET Column1=Column2'

will return a list of the update statements. Copy them to the clipbaord and
paste into a Query Analyzer window, and you're done.

You could get more elaborate and use a cursor to actually execute
everything, but why bother? It sounds like a one-off operation.

If you're using SQL Server 2000, you can compile that table of names with

INSERT INTO TNames (Tname)
SELECT TABLE_NAME
FROM information_schema.tables
where TABLE_TYPE='BASE TABLE'
and TABLE_NAME in (
SELECT TABLE_NAME
FROM information_schema.columns
where COLUMN_NAME='Column2')
and TABLE_NAME in (
SELECT TABLE_NAME
from information_schema.columns
where COLUMN_NAME='Column2')

[Greg D. Moore \(Strider\)]

"Muzamil" <mu*****@hotmail.com> wrote in message
news:5a**************************@posting.google.c om...

Hi

My database contains , lets say, 100 tables. Out of these 100 tables,
60 tables contain both columns; Column1 and Column2.

I want to:

Update (All tables with BOTH of these columns)

Set Column1 = Column2

Is there a better way of doing it? OR do I have to do it manually for
each table.

There is an UNDOCUMENTED SP in master sp_msforeachtable.

Note: Microsoft does NOT support the use of this. It may change in any
servicepack or update.

If you're careful for what you want though, it may be useful.

Thanks for your help.

[Muzamil]

Thanks everyone.
I used the following code and it works like a charm.
declare
@DbName sysname,
@IdCol sysname,
@CodeColumn sysname

CREATE TABLE #TableWithBothColumns (
TabName varchar(500),
ColumnID varchar(20),
CodeColumn Varchar(500) NOT NULL)

select @Dbname = 'DB', @idcol= 'col1', @codecolumn= 'col2'

exec (

'USE '+ @DbName +' insert into #TableWithBothColumns SELECT distinct
a.name, ''' + @IdCol + ''' AS IDColumn , ''' + @CodeColumn + ''' AS
codeColumn FROM sysobjects a JOIN syscolumns b ON a.id = b.id WHERE
a.type = ''U''
and a.id in (
select distinct c.id from syscolumns c
inner join syscolumns s
on c.id = s.id
where c.name = '''+ @IdCol +''' and s.name = '''+@CodeColumn +''')'
)


declare @table_name sysname

LoopTable:

set @table_name = NULL

select top 1 @table_name = Tabname FROM #TableWithBothColumns

print 'tabename is '+ @table_name

if NOT (@table_name IS NULL)
begin

exec ( ' Update [' + @table_name + '] Set col1=col2')

delete from #TableWithBothColumns where tabname = @table_name
goto LoopTable
end
drop table #TableWithBothColumns
"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<_s*******************@twister.nyroc.rr.com>. ..

"Muzamil" <mu*****@hotmail.com> wrote in message
news:5a**************************@posting.google.c om...

Hi

My database contains , lets say, 100 tables. Out of these 100 tables,
60 tables contain both columns; Column1 and Column2.

I want to:

Update (All tables with BOTH of these columns)

Set Column1 = Column2

Is there a better way of doing it? OR do I have to do it manually for
each table.

There is an UNDOCUMENTED SP in master sp_msforeachtable.

Note: Microsoft does NOT support the use of this. It may change in any
servicepack or update.

If you're careful for what you want though, it may be useful.

Thanks for your help.

[--CELKO--]

>> I want to:

UPDATE {{All tables with BOTH of these columns}}
SET column1 = column2; <<

The short answer is to use dynamic SQL. Of course this is a sure sign
of poor coding and design, it is proprietary and dangerous.

The real question is: "what are you trying to do?" The goal of
normalization is to remove redundancy and you seem to be adding it
into 100 tables.