3

This is just a lazy thought. I have a table with about 9 columns names. Is there any way I can use SQL statement to return only the Column names? The normal way is to write out my column names from 

SELECT * FROM tableName;

statement but was wondering if I can get the column names with SQL statement.

Any Ideas would be appreciated.

Thanks You!

Guzzyman
  • 561
  • 6
  • 16
  • 37
  • 1
    I suppose that this is a request for Sql Server right? – Steve Dec 31 '13 at 11:10
  • 1
    What DBMS are you using? If it is SQL Server I would be inclined to use the system views (e.g. sys.columns) rather than the information schema for reasons outlined [in this article by Aaron Bertrand](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views) – GarethD Dec 31 '13 at 11:11
  • Hi Bro, this statements would be used via the code behind file in an ASP.NET application. I need to get the result set then parse the returned values to a dropDownList control which will then be used for further quesries. So in a nutshell...would be using SQL Server when the application is finally deployed to the life Server. – Guzzyman Dec 31 '13 at 11:17
  • possible duplicate of [How can I get column names from a table?](http://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table) – Ramesh Rajendran Dec 31 '13 at 11:27
  • @RameshRajendran that question is about Oracle – stuartd Dec 31 '13 at 12:10
  • @stuartd Not oracle only.about sql there – Ramesh Rajendran Dec 31 '13 at 12:16
  • @stuartd, See my answer here : http://stackoverflow.com/a/20856606/2218635 – Ramesh Rajendran Dec 31 '13 at 12:16
  • @RameshRajendran the question is tagged Oracle, and the accepted answer is querying the `USER_TAB_COLUMNS` table. The fact that most of the answers are for SQL Server doesn't change that. And why have you posted a link to your answer in this question? – stuartd Dec 31 '13 at 13:19
  • possible duplicate of [Get columns of a table SQL SERVER](http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server) – OGHaza Jan 02 '14 at 13:35

7 Answers7

5
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName' AND TABLE_SCHEMA='dbo'

Should do it

MichaC
  • 13,104
  • 2
  • 44
  • 56
1
SET FMTONLY ON
SELECT * FROM tablename
Sandip Bantawa
  • 2,822
  • 4
  • 31
  • 47
0

Open you Microsoft SQL Server Management Studio, in New Query, Just type your Table name and select it and then press ALT + F1 Key, it will give all details about table

शेखर
  • 17,412
  • 13
  • 61
  • 117
Manoj Mevada
  • 649
  • 4
  • 7
0

Try following SQL statement to get column name.

SELECT column_name   
FROM information_schema.columns 
WHERE TABLE_NAME='tableName'
Sandip Bantawa
  • 2,822
  • 4
  • 31
  • 47
Sagar Rawal
  • 1,442
  • 2
  • 12
  • 39
0

You can query the syscolumns table for table column metadata.

 SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE  [Name] = 'TableName')

See this image that query return values

enter image description here

Referred from : How can I get column names from a table in Oracle?

and another one way

Community
  • 1
  • 1
Ramesh Rajendran
  • 37,412
  • 45
  • 153
  • 234
  • Thanks, I tried this method alongside with the others in the answers to my question but I got only the COLUMN NAME header as my result. So decided to use a datatable to get the result of the normal sql SELECT * FROM tableName statement, binded it to a gridView, used the HeaderRow.Count property of the GridView to get the HeaderNames, passed same into a DataTable, then Populated my Drop Down List Control with the results. – Guzzyman Dec 31 '13 at 16:43
0

select column_name,* from information_schema.columns where table_name = 'YourTableName' order by ordinal_position

Karan Kular
  • 9
  • 1
0

Using SQL Server xml raw you get the column names from any sql query. Replace 'select top 1 * from [TABLENAME]' from the example below. Query must return at least 1 row of data and remember to use top 1.

declare @t1 table (x xml)
declare @t2 table (x xml)
declare @t3 table (x xml)

insert into @t1 select cast( (select top 1 * from [TABLENAME] for xml raw) as xml)
insert into @t2 select y.r.query('.') from @t1 cross apply x.nodes('/row') as y(r)
insert into @t3 select t2.n.value('local-name(.)', 'varchar(max)') from @t2 t cross apply x.nodes('//@*') as t2(n)

select replace (convert (nvarchar, x),'_x0020_', ' ') from @t3
Teemu Lehto
  • 1