none
How to get all field names in a table using sql query?

    Question

  • Hi,

    How canI get all field names in a table using sql query? Now I am not interested in the data in this table, what I am interested in is just the schema, i.e. all field names. Thanks.

    Monday, September 11, 2006 8:39 PM

Answers

All replies

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

    Denis the SQL Menace
    http://sqlservercode.blogspot.com/

    Monday, September 11, 2006 8:51 PM
  • Thanks a lot. It works.
    Tuesday, September 12, 2006 12:07 PM
  • How to get a list of all field names in all tables?

     

    Thanks!

    Monday, January 28, 2008 3:40 PM
  • If you are working with SQL Server 2005, try this:

    select t.name as TableName, c.name as ColumnName from sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    order by t.name
    Monday, January 28, 2008 4:57 PM
  • Code Snippet

    SELECT Table_Name, Column_Name

    FROM information_schema.columns
    ORDER BYTable_Name, Ordinal_Position

     

     

    Monday, January 28, 2008 5:06 PM
  • Want a familiar format try:

     

    select

    column_name + ' (' + data_type + case isnull(character_maximum_length,'') when '' then '' else '(' + cast (character_maximum_length AS varchar(6)) + ')' end + case is_nullable when 'YES' then ', null)' else ', not null)' end AS Columns

    from information_schema.columns

    where table_name = 'YourTableName'

    order by ordinal_position

     

    • Proposed as answer by spvarapu Wednesday, April 21, 2010 8:40 AM
    Friday, August 01, 2008 8:33 PM
  • how to do it for excel, where we use Jet Engine to connect.
    I want to apply where criteria on it.
    pls help...
    fazal.
    Wednesday, November 12, 2008 6:37 AM
  • thanx
    Wednesday, December 01, 2010 8:00 AM
  • Just to add to this library of answers a much better solution for any other nomadic inquisitors who may happen upon this post. If you have access to the stored procedures on the MSSQL Server, by FAR the EASIEST way to do this is to call the sp_columns system stored procedure. It will return more information than you probable care to know :P

    Syntax: sp_columns [table name]

    Cheers :)

    Saturday, July 30, 2011 12:29 AM
  • select * from [your table name] where 1=0

    Nadia Azmi


    • Edited by Dia.Agha Saturday, May 05, 2012 2:47 AM
    Friday, May 04, 2012 2:18 PM
  • nadia can i ask, using your approach how would you get column names usable as rows in a new table
    Friday, August 31, 2012 10:57 AM
  • its nice .I will try this today
    Saturday, January 05, 2013 5:53 AM
  • I am sorry, didnt see your question, if thats the case, use this

     SELECT A.Table_name ,b.Column_name
    FROM INFORMATION_SCHEMA.TABLES A
    inner join INFORMATION_SCHEMA.COLUMNS b
    on b.TABLE_NAME=a.TABLE_NAME 
    where A.TABLE_NAME='orders'
    
    
    Or if you need datatype too
    
     SELECT A.Table_name ,b.Column_name,b.DATA_TYPE 
    FROM INFORMATION_SCHEMA.TABLES A
    inner join INFORMATION_SCHEMA.COLUMNS b
    on b.TABLE_NAME=a.TABLE_NAME 
    where A.TABLE_NAME='orders'
    
    
    or i would suggest check both A and B and see what you need
    
     SELECT A.*,b.*
    FROM INFORMATION_SCHEMA.TABLES A
    inner join INFORMATION_SCHEMA.COLUMNS b
    on b.TABLE_NAME=a.TABLE_NAME 
    where A.TABLE_NAME='orders'


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Saturday, January 05, 2013 5:14 PM
  • Hello Everyone,

    Below is an alternate solution.

    sp_columns 'TableName'


    Hope this helps!

    Many Thanks & Regards,
    Mehul Bhadricha


    Thursday, June 06, 2013 7:05 AM
  • sp_columns does not work with two part table names:

    exec sp_columns @table_name = 'SalesOrderDetail';
    /*  Partial results
    COLUMN_NAME	TYPE_NAME
    SalesOrderID	int
    SalesOrderDetailID	int identity
    CarrierTrackingNumber	nvarchar
    OrderQty	smallint
    ProductID	int
    SpecialOfferID	int
    UnitPrice	money
    UnitPriceDiscount	money
    LineTotal	numeric
    rowguid	uniqueidentifier
    ModifiedDate	datetime
    */
    exec sp_columns @table_name = 'Sales.SalesOrderDetail';
    -- (0 row(s) affected)


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, June 14, 2013 2:05 PM
  • Create function [dbo].[AF_TableColumns](@table_name nvarchar(55))
    returns nvarchar(4000) as
    begin
    declare @str nvarchar(4000)
     select @str = cast(rtrim(ltrim(column_name)) as nvarchar(500)) + coalesce('   ' + @str , '   ')
     from information_schema.columns
     where table_name = @table_name
     group by table_name, column_name, ordinal_position
     order by ordinal_position DESC
    return @str
    end

    --select dbo.AF_TableColumns('YourTable') Select * from YourTable

    Tuesday, October 01, 2013 12:37 AM
  • thank you.. :)
    Saturday, January 11, 2014 8:32 AM
  • Enhancing Jean Paul's UDF with schema support:

    Create function [dbo].[AF_TableColumns](@table_schema sysname = 'dbo', @table_name sysname)
     returns nvarchar(4000) as
     begin
     declare @str nvarchar(4000)
      select @str = cast(rtrim(ltrim(column_name)) as nvarchar(500)) + coalesce('   ' + @str , '   ') 
      from information_schema.columns
      where table_schema=@table_schema and table_name = @table_name
      group by table_name, column_name, ordinal_position 
      order by ordinal_position DESC
     return @str
     end
    go
    --select dbo.AF_TableColumns('Production','Product') 
    --select dbo.AF_TableColumns(DEFAULT, 'DimProduct') 
    


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, January 12, 2014 10:05 PM
  • If you check sp_columns stored procedure code, you'll find out the correct syntax:

    exec sp_columns @table_name = 'SalesOrderDetail', @table_owner = 'Sales';


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Shrik36 Wednesday, January 15, 2014 12:15 PM
    • Marked as answer by Kalman TothModerator Saturday, January 18, 2014 10:24 PM
    Monday, January 13, 2014 9:37 PM
  • SQL Query to find columnIid:

    SELECT column_id,name   FROM sys.columns WHERE object_id = OBJECT_ID('<tablename>')

    SQL Query to find field names:

    SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('<tablename>') and column_id='<columnid>'


    shrik36

    Wednesday, January 15, 2014 12:16 PM