Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2012-02-06 : 02:19:25
|
| Hi,How is it possible so that any column names of a table if contains a word such as 'a test' then that to be replaced with another word such as 'a tested'.Note that there may be various number of fields in this case so the sql has to be dynamic.I do not think I can use EXEC tempdb..sp_rename because that only does it if you tell the system what field you would like to change...Thanks |
|
|
ramakant_miet
Starting Member
1 Post |
Posted - 2012-02-06 : 02:39:51
|
| Hi, the little bit related question is also from myside,i have Dev database name in approx 100 sps. After moving into UAT i want to replace Dev database name with UAT database name in all 100 sps. Is it possible in one action. Please suggest.Thanks,Ramakant |
 |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-02-06 : 02:49:46
|
| Try something like this by passing arguments dynamicallySelect 'Sp_rename '''+o.name+'.'+ c.name+ ''', ''new_column_name'', ''COLUMN''' from sys.syscolumns c inner join sys.sysobjects o onc.id=o.id and o.xtype='u' and c.name like '%id%'Senthil Kumar C------------------------------------------------------MCITP - Database Administration SQL SERVER 2008MCTS - Database Development SQL SERVER 2008 |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-02-06 : 02:52:49
|
quote:
Originally posted by ramakant_miet
Hi, the little bit related question is also from myside,i have Dev database name in approx 100 sps. After moving into UAT i want to replace Dev database name with UAT database name in all 100 sps. Is it possible in one action. Please suggest.Thanks,Ramakant
Take a script of all the Sp's and run where ever u want..Senthil Kumar C------------------------------------------------------MCITP - Database Administration SQL SERVER 2008MCTS - Database Development SQL SERVER 2008 |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-02-06 : 02:53:39
|
quote:
Originally posted by senthil_nagore
Try something like this by passing arguments dynamicallySelect 'Sp_rename '''+o.name+'.'+ c.name+ ''', ''new_column_name'', ''COLUMN''' from sys.syscolumns c inner join sys.sysobjects o onc.id=o.id and o.xtype='u' and c.name like '%id%'Senthil Kumar C------------------------------------------------------MCITP - Database Administration SQL SERVER 2008MCTS - Database Development SQL SERVER 2008
Thanks for the reply but this does not seem to answer my question. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-02-06 : 12:52:52
|
| http://msdn.microsoft.com/en-us/library/ms190273.aspx ?? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-06 : 13:19:57
|
quote:
How is it possible so that any column names of a table if contains a word such as 'a test' then that to be replaced with another word such as 'a tested'.
First question: why are you renaming the columns?quote:
Note that there may be various number of fields in this case so the sql has to be dynamic.
Again, why are you renaming them?quote:
I do not think I can use EXEC tempdb..sp_rename because that only does it if you tell the system what field you would like to change...
Um, yes, if you're going to rename a column, or set of columns, you have to know which ones to rename. How else did you plan on doing it?BTW, if you're planning on changing "a test" to "a tested" please recognize that the new name contains the old name, and if you will be renaming on partial matches you'll get an infinite loop with such things. |
|
|
|
|
|
|
|
|
change field name dynamically