List SQL Server table column name as rows

[caper11752]

Hello,

Let's say I have a table called tblMyTable and it has 3 columns, MyId,FirstName,LastName. Is it possible to build a query that would return the column names as row values under a new column? Something like this:

MyNewColumnName
MyId
FirstName
LastName

I've done plenty of searching but couldn't find anything that actually worked. I need this query to populate values for a parameter in Report Builder. Thanks in advance.

 

[simian336]

Try...

select Name
from sys.columns
where object_id = (select object_id from sys.tables where name='yourtable')
order by name

Simi

 

[simian336]

or

select c.Name
from sys.columns c
join sys.tables t
on c.object_id=t.object_id
where t.name='tblMyTable'
order by c.name

Simi

 

[simian336]

or

select c.Name
from sys.columns c
join sys.tables t
on c.object_id=t.object_id
where t.name='tblMyTable'
order by c.name

Simi

 

[caper11752]

Ah......that would probably work but it appears that i don't have sufficient privileges to access the sys.tables. Thanks though for the quick response.

Are there any other methods that I could use?

 

[gmmastros]

Select Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'tblMyTable'
Order By Column_Name

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[caper11752]

Finally got it to work. Thank you both for your input, I really appreciate it!