uceqELtK

1Declare @TableName  [nvarchar](128)
2Declare @ExecStr    nvarchar(max)
3Declare @Where      nvarchar(max)
4Set @TableName = 'myTableName'
5Set @Where = ''
6
7--Set @ExecStr = N'Select * From '+quotename(@TableName)+@Where
8--Exec(@ExecStr)
9
10Drop Table If Exists #tmp_Col2Row
11
12Create Table #tmp_Col2Row
13(Field_Name nvarchar(128) Not Null
14,Field_Value nvarchar(max) Null
15)
16
17Set @ExecStr = N' Insert Into #tmp_Col2Row (Field_Name , Field_Value) '
18Select @ExecStr += (Select N'Select '''+C.name+''' ,Convert(nvarchar(max),'+quotename(C.name) + ') From ' + quotename(@TableName)+@Where+Char(10)+' Union All '
19         from sys.columns as C
20         where (C.object_id = object_id(@TableName)) 
21         for xml path(''))
22Select @ExecStr = Left(@ExecStr,Len(@ExecStr)-Len(' Union All '))
23--Print @ExecStr
24Exec (@ExecStr)
25
26Select * From #tmp_Col2Row
27Go