Geek Answers Handbook

Rename SQL table columns with table field values

I am trying to execute an SQL query that will rename the columns of a table with text from the first set of records in a table.

My table looks like this:

 COL1  |  COL2  |  COL3  |  COL4  |  COL5  | COL6

 REASON |ITEMDATE|ITEMTIME|SITENAME|  EVENT | RPM  
 tstamp |12-11-07|  24:12 | Spain1 |Shutdwn | 1000  
 tstamp |13-11-07|  02:22 | Spain1 |Startup | 1050

And I would like to rename the columns as follows:

 REASON |ITEMDATE|ITEMTIME|SITENAME|  EVENT | RPM

 tstamp |12-11-07|  24:12 | Spain1 |Shutdwn | 1000  
 tstamp |13-11-07|  02:22 | Spain1 |Startup | 1050
+5
source share
3 answers

This procedure will do what you need. You can run it as follows:

    exec p_rename_columns N'<mytable>'

Note that the procedure assumes that the “first” line is the physical first line on the disk. Since this may change depending on which field the clustered index in the table is used in, it is not 100% guaranteed.

Procedure Code:

create proc p_rename_columns (@table sysname)
AS

declare @name sysname, 
        @col sysname,
        @sql nvarchar(max)

declare cur cursor 
local read_only 
for select name 
      from sys.columns 
     where object_id = object_id(@table)

open cur
fetch next from cur into @name

while @@fetch_status = 0 
  begin

    select @sql = N'select top (1) @col = ' + quotename(@name) + N' from ' + quotename(@table)
    exec sp_executesql @sql, N'@col sysname output', @col output

    select @sql = N'exec sp_rename ''' + quotename(@table) + N'.' + quotename(@name) + N''', ''' + @col + N''''
    exec (@sql)

    fetch next from cur into @name
  end 
close cur
deallocate cur

select @sql = N'DELETE TOP (1) from ' + quotename(@table)
exec (@sql)
GO
+4

SQL , - SQL- .

, , ? , - , . CSV , SSIS, .

+2

. , SSIS, , (ad-hoc-). , T-SQL () Cross Apply , .

:

  • , 10 20 , , .
  • If you have many tables, say more than 20 tables, or you want to do this process many times (part of a larger solution), just do what you want to do in .NET. In other words, get a list of all the tables in your database in your code, then for each table, get the first row, then create a new table with the appropriate column headers, etc. The main thing is that you have a lot in the .NET environment than in T-SQL.
0
source

More articles:


All Articles