Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to rename a column in all tables and views Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 12:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
Hi
I want to change the name of a column in all tables and views

(rename Feild "Phone" To "TelePhone")

Sp-ReName Change only one table can be done

Plz Help Me
Post #1502306
Posted Monday, October 7, 2013 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
babak3334000 (10/7/2013)
Hi
I want to change the name of a column in all tables and views

(rename Feild "Phone" To "TelePhone")

Sp-ReName Change only one table can be done

Plz Help Me


You first have to identify which table(s) contain that column. You can use sys.columns to find them. Then you will need to change the name in ALL of the tables.

For your views, you will have to alter every single view. There just isn't a shortcut for this but using the sys catalogs can help minimize the effort.

The below query should help you identify everywhere you have a column named "Phone".

select * 
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
where sc.name = 'Phone'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502312
Posted Monday, October 7, 2013 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
After finding the table name command to rename writing
Post #1502325
Posted Monday, October 7, 2013 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
babak3334000 (10/7/2013)
After finding the table name command to rename writing


I think you mean how do you rename the column once you find it?

You mentioned the proc in your first post (sp_rename).

http://technet.microsoft.com/en-us/library/ms188351.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502331
Posted Monday, October 7, 2013 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
I mean that this command can not be used (Sp-rename)

It does not matter to me what can be done with the command

Just change the column name in all tables should be

Plz Help Me Again
Post #1502336
Posted Monday, October 7, 2013 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
babak3334000 (10/7/2013)
I mean that this command can not be used (Sp-rename)

It does not matter to me what can be done with the command

Just change the column name in all tables should be

Plz Help Me Again


Why can you not use sp_rename???


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502345
Posted Monday, October 7, 2013 1:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 40,438, Visits: 36,895
babak3334000 (10/7/2013)
Just change the column name in all tables should be


The way you change column names in a table is with sp_rename. There's no command that automagically changes the column name in all tables, you have to do it one by one with sp_rename.

I suppose you could drop and recreate all the tables to change the column names, but I suspect that's a little more work.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1502348
Posted Monday, October 7, 2013 1:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
The Name tables should be placed as one

EXEC sp_rename N'TableName1.Phone', N'Telephone', N'COLUMN';
EXEC sp_rename N'TableName2.Phone', N'Telephone', N'COLUMN';

There are many Tables and Views
Post #1502352
Posted Monday, October 7, 2013 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
babak3334000 (10/7/2013)
The Name tables should be placed as one

EXEC sp_rename N'TableName1.Phone', N'Telephone', N'COLUMN';
EXEC sp_rename N'TableName2.Phone', N'Telephone', N'COLUMN';

There are many Tables and Views


That looks to my like it would work just fine for these two tables. You could use the original query that I posted to help you build your sql. As has been said by both myself and Gail, there is no shortcut here. You are going to have to run this command on all the tables with the column you want to rename.

Something like this?

select 'EXEC sp_rename N''' + sch.name + '.' + so.name + '.Phone'', N''TelePhone'', N''COLUMN'';',
*
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
join sys.schemas sch on sch.schema_id = so.schema_id
where sc.name = 'Phone'


--EDIT--

Fixed a typo in the code.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502354
Posted Monday, October 7, 2013 2:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60

select 'EXEC sp_rename N''' + sch.name + '.' + so.name + '.Phone'', N''TelePhone'', N''COLUMN'';',
*
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
join sys.schemas sch on sch.schema_id = so.schema_id
where sc.name = 'Phone'


Do not
Post #1502360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse