Forum Discussion

Jagdeep_1012's avatar
Jagdeep_1012
Copper Contributor
Mar 05, 2024

Updating columns names of multiple tables dynamically

Hi all

 

I have 10 tables and the columns names have same prefix names but different suffix after underscore - my requirement is I want to update the column names with the prefix only means I want to remove the underscore and the value after underscore in column name 

Example 

Abc_12345 

Def_456

Suppose above 2 are column name and I want to update it with 

Abc

Def 

These will be new columns name 

I want to do this with 10 tables dynamically

 

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Mar 06, 2024

    I want to do this with 10 tables dynamically

    Jagdeep_1012 , 10 tables x 2 columns = 20 columns? It would be fast and much more safer doing it manually.

  • frugecn's avatar
    frugecn
    Copper Contributor
    Mar 06, 2024

    If you have a database project, you could just update them there and do a sync.  Another way is to create a cursor for the tables and columns sys views filtered to what you tables and columns you wanted and do sp_rename on the columns with the new generated from LEFT(column_name, CHARINDEX('_',column_name)-1) assuming that the first '_' is where you want to split from.  I'm sure there are much more elegant ways as well.

Resources