Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

List SQL Server table column name as rows

Status
Not open for further replies.

caper11752

IS-IT--Management
May 6, 2008
26
CA
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.
 
Try...

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

Simi
 
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
 
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
 
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?
 
Code:
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
 
Finally got it to work. Thank you both for your input, I really appreciate it!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top