How can I get column names from a table in SQL Server?

I would like to query the name of all columns of a table. I found how to do this in:

But I need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

28.06.2009 14:05:50
As a quick&dirty trick, I really like to do SELECT * FROM my_table WHERE 1=0
bgusach 15.02.2016 12:29:28
@bgusach - Seems like the user wanted the names of columns as rows in a table, but for what you're trying to do, SELECT TOP 0 * FROM my_table is less keystrokes
Jake Wood 2.02.2017 17:07:25
@bgusach: That sounds like an answer to me. You should post it.
palswim 8.02.2019 19:27:35
19 ОТВЕТОВ
РЕШЕНИЕ

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

842
18.06.2019 21:23:26
what does "N" in "= N'Customers'" stands for ?
Qbik 10.06.2014 09:11:08
Qbik "N" if for handlling unicode string like varchar in ANSI(32bit) and nvarchar in unicode(64bit)
thatsalok 25.06.2014 12:36:15
confirmed: works for MariaDB too ! :) (without Nortwind. ... )
jave.web 31.03.2016 09:46:08
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tableName'; this works for me
Pavol Travnik 18.01.2019 15:13:57
I had to use TABLE_SCHEMA = '?' AND TABLE_NAME = '?' since I'm on localhost and I have multiple tables with the same name but in different databases.
akinuri 25.10.2019 11:12:54

You can use the stored procedure sp_columns which would return information pertaining to all columns for a given table. More info can be found here http://msdn.microsoft.com/en-us/library/ms176077.aspx

You can also do it by a SQL query. Some thing like this should help:

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName') 

Or a variation would be:

SELECT   o.Name, c.Name
FROM     sys.columns c 
         JOIN sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U' 
ORDER BY o.Name, c.Name

This gets all columns from all tables, ordered by table name and then on column name.

194
15.06.2019 22:23:24

By using this query you get the answer:

select Column_name 
from Information_schema.columns 
where Table_name like 'table name'
44
26.03.2014 14:21:04
--This is another variation used to document a large database for conversion (Edited to --remove static columns)

SELECT o.Name                   as Table_Name
     , c.Name                   as Field_Name
     , t.Name                   as Data_Type
     , t.length                 as Length_Size
     , t.prec                   as Precision_
FROM syscolumns c 
     INNER JOIN sysobjects o ON o.id = c.id
     LEFT JOIN  systypes t on t.xtype = c.xtype  
WHERE o.type = 'U' 
ORDER BY o.Name, c.Name

--In the left join, c.type is replaced by c.xtype to get varchar types
27
15.06.2019 22:21:43
SELECT column_name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable 
FROM information_schema.COLUMNS WHERE table_name LIKE 'YOUR_TABLE_NAME'
ORDER BY ordinal_position
15
23.02.2012 18:14:14
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

This is better than getting from sys.columns because it shows DATA_TYPE directly.

147
17.06.2016 15:51:54
+1 since this is standard ANSI SQL (en.wikipedia.org/wiki/Information_schema) Other answers like sys.objects are not standard
Reversed Engineer 16.11.2016 09:10:12
And if the table is in another schema (the SQL server "schema" variant) add AND TABLE_SCHEMA = 'schemaName' in the WHERE clause.
Johan 21.12.2016 19:34:39
Very useful, and you can add JOIN sys.types t on c.system_type_id = t.system_type_id and add t.name in your 'SELECT' statement to get the types next to each column name as well.
Pac0 3.11.2017 13:50:13

You can use sp_help in SQL Server 2008.

sp_help <table_name>;

Keyboard shortcut for the above command: select table name (i.e highlight it) and press ALT+F1.

56
15.06.2019 22:22:50
this is my all time favorite keyboard short-cut. I also assign sp_helptext to Cntl-F1. Together these two shortcuts save so much time!
Paul Wehland 21.09.2018 10:57:25

It will check whether the given the table is Base Table.

SELECT 
    T.TABLE_NAME AS 'TABLE NAME',
    C.COLUMN_NAME AS 'COLUMN NAME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
    WHERE   T.TABLE_TYPE='BASE TABLE'
            AND T.TABLE_NAME LIKE 'Your Table Name'
9
8.04.2013 14:28:01
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

TABLE_NAME is your table

22
15.06.2019 22:22:11

This SO question is missing the following approach :

-- List down all columns of table 'Logging'
select * from sys.all_columns where object_id = OBJECT_ID('Logging')
11
27.06.2013 19:13:53
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'
1
4.12.2015 16:04:09
Other answers is the same.
Kiquenet 8.02.2017 14:37:22

You can write this query to get column name and all details without using INFORMATION_SCHEMA in MySql :

SHOW COLUMNS FROM database_Name.table_name;
36
19.09.2016 12:37:25
@Benjamin, because this question is for SQL Server and this answer is for MySql
Caimen 29.11.2016 13:47:06
May be most of the peoples using MySql, faces this problem. And I have mentioned it. I am using MySql.
Sachin Parse 20.07.2017 06:01:11
It doesn't matter if most people using other RDBMSes have the same problem, it's irrelevant to the original question and pushes relevant answers further down.
Demonblack 4.10.2018 13:57:51
I downvote because the question is specifically address to mssql
Hartnäckig 20.12.2019 04:04:02

You can try this.This gives all the column names with their respective data types.

desc <TABLE NAME> ;
10
27.06.2016 12:31:04
I know this works in Oracle. But does this work in Microsft SQL? Thank you.
DxTx 18.04.2018 16:57:10

you can use this query

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'
6
30.06.2016 07:43:33
SELECT c.Name 
FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.object_id = OBJECT_ID('TABLE_NAME')
ORDER BY c.Name
6
9.03.2017 21:34:33

Just run this command

EXEC sp_columns 'Your Table Name'
12
19.10.2018 15:57:35
its not giving the desired result
Amit chauhan 22.10.2018 11:10:10

One other option which is arguably more intuitive is:

SELECT [name] 
FROM sys.columns 
WHERE object_id = OBJECT_ID('[yourSchemaType].[yourTableName]') 

This gives you all your column names in a single column. If you care about other metadata, you can change edit the SELECT STATEMENT TO SELECT *.

5
24.09.2018 17:12:09
SELECT TOP (0) [toID]
      ,[sourceID]
      ,[name]
      ,[address]
  FROM [ReportDatabase].[Ticket].[To]

Simple and doesnt require any sys tables

0
5.10.2019 23:31:39

Summarizing the Answers

I can see many different answers and ways to do this but there is the rub in this and that is the objective.

Yes, the objective. If you want to only know the column names you can use

SELECT * FROM my_table WHERE 1=0
or
SELECT TOP 0 * FROM my_table

But if you want to use those columns somewhere or simply say manipulate them then the quick queries above are not going to be of any use. You need to use

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'

one more way to know some specific columns where we are in need of some similar columns

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'
2
25.12.2019 08:35:11