使用表的字段值重命名SQL表的列

问题描述：

我正在尝试执行一个SQL查询，该查询将使用表中第一个记录集的文本来重命名表的列.

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

我想这样重命名列:

 REASON |ITEMDATE|ITEMTIME|SITENAME|  EVENT | RPM

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

答

此过程将满足您的需求.您可以按以下方式运行它:

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

    exec p_rename_columns N'<mytable>'

请注意，该过程假定第一"行是磁盘上的物理第一行.由于这可以根据表上的聚集索引使用哪个字段而改变，因此不能100％保证.

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.

该过程的代码:

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

使用表的字段值重命名SQL表的列

相关推荐