October 7, 2013 at 12:35 pm
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
October 7, 2013 at 12:43 pm
babak3334000 (10/7/2013)
HiI 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 1:17 pm
After finding the table name command to rename writing
October 7, 2013 at 1:22 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 1:33 pm
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
October 7, 2013 at 1:48 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 1:52 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2013 at 1:58 pm
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
October 7, 2013 at 2:03 pm
babak3334000 (10/7/2013)
The Name tables should be placed as oneEXEC 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 2:15 pm
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 :crying:
October 7, 2013 at 2:19 pm
This is done with the command ??
execsp_msforeachtable N'
IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''Col1'')
EXEC (''UPDATE ? SET Col1= case when Col1 = ''''ppp'''' then ''''qqq'''' when Col1 = ''''aaa'''' then ''''xxx'''' end'')'
October 7, 2013 at 2:24 pm
babak3334000 (10/7/2013)
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 :crying:
????
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 2:25 pm
babak3334000 (10/7/2013)
This is done with the command ??execsp_msforeachtable N'
IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''Col1'')
EXEC (''UPDATE ? SET Col1= case when Col1 = ''''ppp'''' then ''''qqq'''' when Col1 = ''''aaa'''' then ''''xxx'''' end'')'
NO. Use the code I posted to help you build the sql you need to execute. I can't figure out what you are trying to do here with sp_msforeachtable. That code is updating the table, it has nothing to do with renaming the column.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 2:33 pm
Sean Lange (10/7/2013)
babak3334000 (10/7/2013)
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 :crying:
????
It does not work
the name of the columns did not change
October 7, 2013 at 2:37 pm
babak3334000 (10/7/2013)
Sean Lange (10/7/2013)
babak3334000 (10/7/2013)
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 :crying:
????
It does not work
the name of the columns did not change
Of course not...it is just a select statement. It does however have all of the sql you need to execute in the first column. Run the query, select the first column, paste that into a window and it should be the rename for your tables. You will probably want to add "and so.type = 'U'" to the query so it only returns tables. You will have to manually update all of your views.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply