Rename the columns of a SQL table with field value

问题:

Im trying to execute a SQL query that will rename the columns of a table with the text from the first recordset 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 like this:

 REASON |ITEMDATE|ITEMTIME|SITENAME|  EVENT | RPM

 tstamp |12-11-07|  24:12 | Spain1 |Shutdwn | 1000  
 tstamp |13-11-07|  02:22 | Spain1 |Startup | 1050 

回答1:

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" row is the physical first row on disk. Since this can change depending on which field the clustered index on the table uses it is not 100% guaranteed.

The code for the procedure:

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

回答2:

There's no simple way in pure SQL, the main options are to build a dynamic SQL query or just return one single result set and let your presentation layer handle it.

But, the bigger question is why are you mixing data and metadata in the same table? If one row has the column names and everything else is data, then just create a table with the correct column names and load the data into it. Since you appear to be loading a CSV file, you could use SSIS to do most of the work for you.

回答3:

Once we had a similar problem. Learning other tools like SSIS has a high learning curve and won't be economical in your case, because you only want to use it once here (an ad-hoc solution). On the other hand, trying to write it in pure T-SQL needs accessing Catalog Views (metadata) and mixing them with cursors, or using Cross Apply function for each table, which is also difficult.

I propose a simpler solutions:

1. If you only want to do it once, and your tables are not more than say 10 or 20 tables, simply create them by hand in a new database, and then write a simple insert query to populate new tables, and remove undesired rows.
2. If you have many tables, say more than 20 tables, or you want to do this process many times (part of a larger solution), then simply do what you want to do in .NET. In other words, get the list of all tables of your database in your code, then for each table, get the first row, then create a new table with the corresponding column titles, etc. The main point here is that, you have much power in .NET's environment, than in T-SQL.