Total Replacing

[Dmitri Shvetsov]

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

[Simon Hayes]

Re: Total Replacing

"Dmitri Shvetsov" <dshvetsov@cox. net> wrote in message news:<UgEnb.949 48$Ms2.65755@fe d1read03>...[color=blue]
> 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[/color]

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', 'Column1Updated Name'
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_SCH EMA.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

[Simon Hayes]

Re: Total Replacing

"Dmitri Shvetsov" <dshvetsov@cox. net> wrote in message news:<UgEnb.949 48$Ms2.65755@fe d1read03>...[color=blue]
> 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[/color]

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', 'Column1Updated Name'
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_SCH EMA.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

[John Bell]

Re: Total Replacing

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_SCH EMA.COLUMNS view should help cut down the need to
link multiple system tables.

John


"Dmitri Shvetsov" <dshvetsov@cox. net> wrote in message
news:UgEnb.9494 8$Ms2.65755@fed 1read03...[color=blue]
> 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[/color]
BY[color=blue]
> 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
>
>[/color]

[John Bell]

Re: Total Replacing

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_SCH EMA.COLUMNS view should help cut down the need to
link multiple system tables.

John


"Dmitri Shvetsov" <dshvetsov@cox. net> wrote in message
news:UgEnb.9494 8$Ms2.65755@fed 1read03...[color=blue]
> 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[/color]
BY[color=blue]
> 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
>
>[/color]