Database

@objname	nvarchar(1035),		-- up to 4-part "old" name
@newname	sysname,			-- one-part new name
@objtype	varchar(13) = null	-- identifying the name

EXEC sp_rename 
@objname= 'TABLE_NAME.OLD_COLUMN_NAME', 
@newname = 'NEW_COLUMN_NAME', 
@objtype = 'COLUMN'

CREATE TABLE mytable (
   Name1 VARCHAR(10)
   ,Name2 VARCHAR(10)
   ,Name3 VARCHAR(10)
   ,Name4 VARCHAR(10)
 )

INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( 'N1', 'N2', 'N3', 'N4' ) 
 INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '1', '2', '4', '7' ) 
 INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '3', '1', '8', '7' ) 
 INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '4', '8', '5', '7' ) 
 INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '7', '5', '3', '8' ) 

SELECT  *
FROM    mytable
;
WITH    unpvt
          AS ( SELECT   col1,
                        col2
               FROM     mytable UNPIVOT ( col2 FOR col1 IN ( [Name1], [Name2], [Name3], [Name4] ) ) AS unpvt
             ) ,
        Result1
          AS ( SELECT   col1 AS NameList_pre,
                        col2 AS NameList
               FROM     unpvt
               WHERE    col2 LIKE 'N%'
             ) ,
        Result2
          AS ( SELECT   col1 AS NameList_pre,
                        col2 AS Tokens
               FROM     unpvt
               WHERE    col2 NOT LIKE 'N%'
             )
    SELECT  Result1.NameList,
            Result2.Tokens
    FROM    Result2
            INNER JOIN Result1 ON Result1.NameList_pre = Result2.NameList_pre
    ORDER BY Result1.NameList,
            Result2.Tokens

Name1      Name2      Name3      Name4
                       
N1         N2         N3         N4
1          2          4          7
3          1          8          7
4          8          5          7
7          5          3          8

Use myDatabase
GO

select so.name,sc.name 
from sysobjects so, syscolumns sc 
where so.xtype = 'U' 
  and so.name = 'myTable1' 
  and so.id = sc.id

Col1    Col2    Col3
10      20      30
100     200     300
1000    2000    3000
10000   20000   30000

SELECT T2.CODE, T1.COLS,T1.INDICOLVALS 
FROM 
(
	SELECT INDICOLVALS,COLS

    FROM 

         (SELECT Col1, Col2, Col3 FROM Table1) P

    UNPIVOT

         (INDICOLVALS FOR COLS IN (Col1, Col2, Col3)) AS U
) AS T1

INNER JOIN TABLE2 T2

ON T1.INDICOLVALS = 10*T2.CODE

declare @cols nvarchar(2000)
declare @sql nvarchar(4000)

set @cols='Col1, Col2, Col3'

set @query='SELECT T2.CODE, 
T1.COLS,
T1.INDICOLVALS 
FROM 
(	
SELECT INDICOLVALS,COLS    FROM          

(SELECT Col1, Col2, Col3 FROM Table1) P    
UNPIVOT         
(INDICOLVALS FOR COLS IN ('+ @cols +')
) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.COLS = T2.[Name]'

--print @query

execute(@query)

Select name from syscolumns where id=object_id('Table 1')