sql server - Build a SELECT statement based on values returned in sys.columns -

so, trying figure out how (if possible) return result set of 2 columns who's rows directly proportional number of columns in given table name, without using dynamic sql function: exec.

ex. have table 'tbla':

create table [dbo].[tbla]( [columna] [varchar](20) not null, [columnb] [varchar](20) not null, [columnc] [varchar](20) not null ) 

and hav tblb

create table [dbo].[tblb]( [column1] [varchar](20) not null, [column2] [varchar](20) not null ) 

i can locate columns running:

 select c.name sys.columns c inner join sys.tables t on c.object_id = t.object_id t.name = 'tablename' 

i want return table of 2 columns # of rows equal number of columns table name querying. example,

select columna, columnb, columnc tbla columna = ???  

would inserted table:

table [dbo].[resulttable]( [columnname] [varchar](max) not null, [columnvalue] [varchar](max) not null ) 

so sp returns looks kinda like:

---------------------------- | columnname | columnvalue | ---------------------------- | columna    | hello       | ---------------------------- | columnb    | world       | ---------------------------- | columnc    | fubar       | ---------------------------- 

but using tblb give you

---------------------------- | columnname | columnvalue | ---------------------------- | column1    | abcde       | ---------------------------- | column2    | 12345       | ---------------------------- 

if tbla has x column, returned set have x row, still 2 columns. know can accomplished using exec function, didn't know if possible without using it?

select columnname,  columnvalue     (select columna, columnb, columnc tbla) p unpivot    (columnvalue columnname in        ([columna], [columnb], [columnc]) )as unpvt;