"Dmitri Shvetsov" <ds*******@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