SEO

#1 (**permalink**) 02-28-2008, 06:33 PM

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

#2 (**permalink**) 02-28-2008, 06:33 PM

"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

#3 (**permalink**) 02-28-2008, 06:33 PM

Hi

The easiest way to do this is probably easiest to do using a CURSOR and EXEC
a call to sp_rename.
See Books Online for more information regarding cursors.

Using the INFORMATION_SCHEMA.COLUMNS view should help cut down the need to
link multiple system tables.

John

"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
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Total Replacing