0

I would like to rename all column names of a table given a string with new names separated by comma. Here be the string:

declare @str varchar(max)='A,B,C'

The number of columns in a table may vary, and appropriately the number of names in a string. So in a 5-column table the string would be 'A,B,C,D,E'.

I have a table with column names as:

col1 | col2 | col3

and in expected results I would like to have:

A | B | C

Update I have tried to follow that path:

SELECT 
 ORDINAL_POSITION
,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'my_temp'
order by ORDINAL_POSITION asc

but I do not know how to split @sql string so that it can be applied to results in the following way:

ORDINAL_POSITION  COLUMN_NAME  sql_string  
1                 Col1         A  
2                 Col2         B  
3                 Col3         C

Then I could easily create a string like:

dynamic_sql='EXEC sp_rename my_table.' + COLUMN_NAME +',' + sql_string +', ''COLUMN'''
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • So the main problem is that you don't know how to select from the SCHEMA table in this output "Col1, Col2, Col3, etc..." ? – Tab Alleman Oct 03 '17 at 13:42
  • 1
    Why do you need to automate a script to rename columns in your table? Renaming a column is something that should happen VERY rarely. If you need to do this regularly it sounds like you have something pretty wrong in your design. – Sean Lange Oct 03 '17 at 13:52
  • @SeanLange I have this case presented here https://stackoverflow.com/questions/8443802/rename-the-columns-of-a-sql-table-with-field-values-of-a-table . But I have a better situation than in that case because I have a string with column names. – Przemyslaw Remin Oct 03 '17 at 13:55
  • 1
    So all you really need is to know how to parse a delimited string? I still say this process screams of things gone horribly wrong. – Sean Lange Oct 03 '17 at 14:05
  • 1
    Here is my choice for a string splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/ It returns the ordinal position of each delimited value. There are several other great choices here. https://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Oct 03 '17 at 14:25
  • @SeanLange please take a look at that https://stackoverflow.com/questions/39752188/split-string-into-table-given-row-delimiter-and-column-delimiter-in-sql-server . It may seem familiar to you. I have column headers in the first part of that string. – Przemyslaw Remin Oct 03 '17 at 14:27
  • Sure. Use the splitter I have referenced many times across both posts now. Take the ordinal position and build your dynamic sql. – Sean Lange Oct 03 '17 at 14:31
  • @SeanLange That splitter looks good:-) Do you know of any place on the web where the DLL of that splitter is available? – Przemyslaw Remin Oct 03 '17 at 14:49
  • It isn't a CLR splitter. It is 100% t-sql. You create it as an inline table valued function in your database. – Sean Lange Oct 03 '17 at 14:53

2 Answers2

2

Using the splitter from Jeff Moden I have referenced in the comments here and the answer on your question you can do this easily. http://www.sqlservercentral.com/articles/Tally+Table/72993/

Here is a full working example. It would be really helpful if you could post sample tables in your questions so we don't have to do that to work on the questions.

if object_id('SomeTable') is not null
    drop table SomeTable

create table SomeTable
(
    Col1 int
    , Col2 int
    , Col3 int
)

--to demonstrate the column names before we change them
select * from SomeTable

declare @NewNames varchar(100) = 'A,B,C'
    , @SQL nvarchar(max) = ''

select @SQL = @SQL + 'EXEC sp_rename ''SomeTable.' + c.name + ''', ''' + s.Item +''', ''COLUMN'';'
from sys.columns c
join dbo.DelimitedSplit8K(@NewNames, ',') s on s.ItemNumber = c.column_id
where object_id = object_id('SomeTable')


select @SQL
--exec sp_executesql @SQL

--demonstrates the column names have been changed.
select * from SomeTable

I have said this previously and I can't in good conscience not mention it again. The fact that you need to do this is a huge red flag that there is something very very wrong with the way you managing your data. Changing column names should not happen very often at all and only when absolutely required. Doing this routinely is a sign that there are really big issues with the process or the design.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • I thought you are talking about CLR splitter mentioned here https://sqlperformance.com/2012/07/t-sql-queries/split-strings – Przemyslaw Remin Oct 03 '17 at 14:53
  • If you want to use the CLR splitter you need to follow the link in Aaron's blog post to Adam's post on the topic which has the code. http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx – Sean Lange Oct 03 '17 at 14:55
0

This would give you the names of all the columns of an existing table in the correct order

select c.name
  from sys.columns c
    inner join sys.objects o on c.object_id = o.object_id 
 where o.name = 'name_of_table'
 order by c.column_id
dhiman
  • 385
  • 3
  • 9
  • This is easy part. Please look at the updated question. I need to split string and apply it accordingly to the results. – Przemyslaw Remin Oct 03 '17 at 14:20
  • https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html would give you that information – dhiman Oct 03 '17 at 14:25
  • run a cursor on the sys.columns query and use the identity in the split to create the sp_rename string – dhiman Oct 03 '17 at 14:26
  • That isn't a great splitter. See my comment on the main question with better options to split strings. – Sean Lange Oct 03 '17 at 14:26
  • And why a cursor? Once the string is split we don't need to go back and look at each column one by one again. – Sean Lange Oct 03 '17 at 14:27