Changing column name without loosing table date

Changing column name without loosing table date

Post by Babu » Fri, 11 Apr 2003 23:40:04



hi,
I am required to write a DB Script that will rename a
column in a table without loosing table Data.
The name of the table is 'Gateway'. The columns that i
have in the 'Gateway' table are
GatewayId
UserId
pwd

After running the script the 'Gateway' table should have
the following column Names
GatewayId
UserID
Password

As you can see, i have changed one of the column names
from 'pwd' to 'Password'.
This change of column name should happen without loosing
the rows in the gateway table. Data values in the
old 'pwd' column should be in the new 'password' column.

How do i do this?
TIA,
Babu

 
 
 

Changing column name without loosing table date

Post by Andrew J. Kell » Sat, 12 Apr 2003 00:07:54


Check out sp_rename in BooksOnLine

--

Andrew J. Kelly
SQL Server MVP


Quote:> hi,
> I am required to write a DB Script that will rename a
> column in a table without loosing table Data.
> The name of the table is 'Gateway'. The columns that i
> have in the 'Gateway' table are
> GatewayId
> UserId
> pwd

> After running the script the 'Gateway' table should have
> the following column Names
> GatewayId
> UserID
> Password

> As you can see, i have changed one of the column names
> from 'pwd' to 'Password'.
> This change of column name should happen without loosing
> the rows in the gateway table. Data values in the
> old 'pwd' column should be in the new 'password' column.

> How do i do this?
> TIA,
> Babu


 
 
 

Changing column name without loosing table date

Post by Babu » Sat, 12 Apr 2003 01:07:19


Thanks Andrew!!! It worked
Babu
 
 
 

Changing column name without loosing table date

Post by kirk coop » Sat, 12 Apr 2003 04:18:48


Babu,

Not sure on DDL specifics for your tables but below is one
method that may work for you.  HTH

Kirk Cooper

CREATE TABLE GateWay
(
GatewayId CHAR(20),
UserId CHAR(20),
pwd CHAR(20)
)

INSERT INTO GateWay VALUES ('1111111111111','kcooper','br549')
INSERT INTO GateWay VALUES ('2222222222222','kcooper','br550')
INSERT INTO GateWay VALUES ('3333333333333','kcooper','br551')
INSERT INTO GateWay VALUES ('4444444444444','kcooper','br552')

-- BEFORE

SELECT * FROM GateWay
--
-- GatewayId            UserId               pwd                  
-- -------------------- -------------------- --------------------
-- 1111111111111        kcooper              br549              
-- 2222222222222        kcooper              br550              
-- 3333333333333        kcooper              br551              
-- 4444444444444        kcooper              br552              
--
-- (4 row(s) affected)

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
EXECUTE sp_rename N'dbo.GateWay.pwd', N'Tmp_Password', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.GateWay.Tmp_Password', N'Password', 'COLUMN'
GO
COMMIT

-- AFTER

SELECT * FROM GateWay

-- GatewayId            UserId               Password            
-- -------------------- -------------------- --------------------
-- 1111111111111        kcooper              br549              
-- 2222222222222        kcooper              br550              
-- 3333333333333        kcooper              br551              
-- 4444444444444        kcooper              br552              
--
-- (4 row(s) affected)


> hi,
> I am required to write a DB Script that will rename a
> column in a table without loosing table Data.
> The name of the table is 'Gateway'. The columns that i
> have in the 'Gateway' table are
> GatewayId
> UserId
> pwd

> After running the script the 'Gateway' table should have
> the following column Names
> GatewayId
> UserID
> Password

> As you can see, i have changed one of the column names
> from 'pwd' to 'Password'.
> This change of column name should happen without loosing
> the rows in the gateway table. Data values in the
> old 'pwd' column should be in the new 'password' column.

> How do i do this?
> TIA,
> Babu

 
 
 

1. Changing column name without loosing table date

hi,
I am required to write a DB Script that will rename a
column in a table without loosing table Data.
The name of the table is 'Gateway'. The columns that i
have in the 'Gateway' table are
GatewayId
UserId
pwd

After running the script the 'Gateway' table should have
the following column Names
GatewayId
UserID
Password

As you can see, i have changed one of the column names
from 'pwd' to 'Password'.
This change of column name should happen without loosing
the rows in the gateway table. Data values in the
old 'pwd' column should be in the new 'password' column.

How do i do this?
TIA,
Babu

2. Database Design for Mere Mortals

3. Changing column name and type without losing content

4. We're building a team!

5. PLS help: Change field structure without loosing data?

6. Multiple groupings

7. Accessing another user's table without qualifying table name with the owner-name

8. DBD error when launching

9. querying column names from a table without the data

10. How to refernce a column without column name

11. How: Pack Paradox-Table without loosing Ref.Integ.

12. audit trails -- looking for the names of tables/columns changed

13. Changing column names in tables