"Dmitri Shvetsov" <dshvetsov@cox.net> wrote in message news:<UgEnb.94948$Ms2.65755@fed1read03>...
> Hi,
>
> Is it possible to do from one script? We have a set of user's tables like
> "tbl%". We can get this list very easy using this script:
>
> SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BY
> name;
>
> We need to change some column names if these names are in a special list
> that we have. What can we do? Use FOR EACH ROW? Or what?
>
> So, I need to get a column list for each table and check if every column
> name is equal to one of the names from the list and then if YES replace it
> by something or add some symbol to this name. Terrible or possible?
>
> Regards,
> Dmitri

Here's one possible approach:

1. Create a table to hold the old and new column names:

create table dbo.TempNames (OldName sysname, NewName sysname)

2. Insert the old and new column names you want:

insert into dbo.TempNames select 'Column1', 'Column1UpdatedName'
insert into dbo.TempNames select 'Column2', 'Column3'
etc.

3. Execute this query, then copy and paste the output, check it and
execute:

select 'exec sp_rename ''' + i.TABLE_NAME + '.' + i.COLUMN_NAME + ''',
''' + t.NewName + ''', ''column'''
from INFORMATION_SCHEMA.COLUMNS i
join dbo.TempNames t
on i.COLUMN_NAME = t.OldName

This makes some assumptions about your logic, but it should give you
an idea.

Simon