893

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

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

2
  • 61
    As a quick&dirty trick, I really like to do SELECT * FROM my_table WHERE 1=0
    – bgusach
    Feb 15, 2016 at 12:29
  • 25
    @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
    Feb 2, 2017 at 17:07

23 Answers 23

1089

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:

6
  • 36
    what does "N" in "= N'Customers'" stands for ?
    – Qbik
    Jun 10, 2014 at 9:11
  • 23
    Qbik "N" if for handlling unicode string like varchar in ANSI(32bit) and nvarchar in unicode(64bit)
    – thatsalok
    Jun 25, 2014 at 12:36
  • 9
    confirmed: works for MariaDB too ! :) (without Nortwind. ... )
    – jave.web
    Mar 31, 2016 at 9:46
  • 15
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tableName'; this works for me Jan 18, 2019 at 15:13
  • 2
    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
    Oct 25, 2019 at 11:12
210

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.

0
163
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

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

3
  • 7
    +1 since this is standard ANSI SQL (en.wikipedia.org/wiki/Information_schema) Other answers like sys.objects are not standard Nov 16, 2016 at 9:10
  • 1
    And if the table is in another schema (the SQL server "schema" variant) add AND TABLE_SCHEMA = 'schemaName' in the WHERE clause.
    – Johan
    Dec 21, 2016 at 19:34
  • 1
    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
    Nov 3, 2017 at 13:50
62

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.

1
  • 3
    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! Sep 21, 2018 at 10:57
48

By using this query you get the answer:

select Column_name 
from Information_schema.columns 
where Table_name like 'table name'
0
45

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;
4
  • 10
    @Benjamin, because this question is for SQL Server and this answer is for MySql
    – Caimen
    Nov 29, 2016 at 13:47
  • 2
    May be most of the peoples using MySql, faces this problem. And I have mentioned it. I am using MySql. Jul 20, 2017 at 6:01
  • 9
    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
    Oct 4, 2018 at 13:57
  • 3
    I downvote because the question is specifically address to mssql
    – Lucas
    Dec 20, 2019 at 4:04
29
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

TABLE_NAME is your table

28
--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
0
17
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
17

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

desc <TABLE NAME> ;
2
  • 1
    I know this works in Oracle. But does this work in Microsft SQL? Thank you.
    – DxTx
    Apr 18, 2018 at 16:57
  • 1
    This solution is the simplest, should be higher up Dec 16, 2020 at 13:30
14

Just run this command

EXEC sp_columns 'Your Table Name'
1
  • 2
    its not giving the desired result Oct 22, 2018 at 11:10
12

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')
10

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'
10

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]'
7

you can use this query

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'
6
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
5

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

You can try using :-

USE db_name;
DESCRIBE table_name;

it'll give you column names with the type.

1
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'
1
  • 1
    Other answers is the same.
    – Kiquenet
    Feb 8, 2017 at 14:37
1
SELECT TOP (0) [toID]
      ,[sourceID]
      ,[name]
      ,[address]
  FROM [ReportDatabase].[Ticket].[To]

Simple and doesnt require any sys tables

0

Some SQL Generating SQL:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  col001 INTEGER
, col002 INTEGER
, col003 INTEGER
, col004 INTEGER
, col005 INTEGER
, col006 INTEGER
, col007 INTEGER
, col008 INTEGER
, col009 INTEGER
, col010 INTEGER
)
;
INSERT INTO test(col001) VALUES(1);
INSERT INTO test(col002) VALUES(1);
INSERT INTO test(col005) VALUES(1);
INSERT INTO test(col009) VALUES(1);
INSERT INTO test VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

SELECT
  CASE ROW_NUMBER() OVER(ORDER BY ordinal_position)
  WHEN 1 THEN 
    'SELECT'+CHAR(10)+'  *'+CHAR(10)+'FROM test'
   +CHAR(10)+'WHERE '
  ELSE
    '   OR '
  END
+ column_name +' IS NOT NULL'
+ CASE ROW_NUMBER() OVER(ORDER BY ordinal_position DESC)
  WHEN 1 THEN 
    CHAR(10)+';'
  ELSE
    ''
  END
  FROM information_schema.columns
  WHERE table_schema='dbo'
    AND table_name = 'test'
ORDER BY
  ordinal_position;

-- the whole scenario. Works for 10 , will work for 100, too:

-- out -----------------------------------------------
-- out  SELECT
-- out   *
-- out FROM test
-- out WHERE col001 IS NOT NULL
-- out     OR col002 IS NOT NULL
-- out     OR col003 IS NOT NULL
-- out     OR col004 IS NOT NULL
-- out     OR col005 IS NOT NULL
-- out     OR col006 IS NOT NULL
-- out     OR col007 IS NOT NULL
-- out     OR col008 IS NOT NULL
-- out     OR col009 IS NOT NULL
-- out     OR col010 IS NOT NULL
-- out ;

0

Simple and doesn't require sys variables:

SHOW COLUMNS FROM suppliers;
0

try using : "desc Table_name" only thing is it will give other details as well like ,Is Null ,Type and constraints

Not the answer you're looking for? Browse other questions tagged or ask your own question.