How do you return the column names of a table

Question

How would I return the column names of a table using SQL Server 2008  i e  a table contains these columns- id  name  address  country and I want to return these as data

User · Answer

CREATE PROCEDURE  dbo   Usp GetColumnName                 TableName varchar 50  AS BEGIN        BEGIN         SET NOCOUNT ON         IF   TableName IS NOT NULL               select ORDINAL POSITION OrderPosition COLUMN NAME ColumnName from information schema columns               where table name   TableName              order by ORDINAL POSITION     END END

User · Answer

DECLARE  col NVARCHAR MAX   SELECT  col  COALESCE  col              COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE  Table name    MxLocations   SELECT  col

User · Answer

This seems a bit easier then the above suggestions because it uses the OBJECT ID   function to locate the table s id   Any column with that id is part of the table   SELECT      FROM syscolumns   WHERE id OBJECT ID  YOUR TABLE      I commonly use a similar query to see if a column I know is part of a newer version is present   It is the same query with the addition of  AND name  YOUR COLUMN   to the where clause   IF EXISTS           SELECT              FROM syscolumns           WHERE id OBJECT ID  YOUR TABLE               AND name  YOUR COLUMN            BEGIN     PRINT  Column found  END

User · Answer

The following seems to be like the first suggested query above but sometime you have to specify the database to get it to work  Note that the query should also work without specifying the TABLE SCHEMA   SELECT COLUMN NAME FROM   YOUR DB NAME INFORMATION SCHEMA COLUMNS WHERE  TABLE NAME    YOUR TABLE NAME  AND TABLE SCHEMA    YOUR DB NAME

User · Answer

I use   SELECT st NAME  sc NAME  sc system type id FROM sys tables st INNER JOIN sys columns sc ON st object id   sc object id WHERE st name LIKE   Tablename

User · Answer

Why not just try this      right click on the table  -  Script Table As -  Create To -  New Query Editor   Window    The entire list of columns are given in the script  Copy it and use the fields as necessary

User · Answer

try this      select   from  lt tablename gt  where 1 2

User · Answer

USE Database  SELECT TABLE NAME TABLE SCHEMA  Column Name   Data type  FROM  INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA  dbo

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME   N TableName

User · Answer

Something like this   sp columns  table name your table name

User · Answer

I just use a query like Martin Smith mentioned  just little shorter   SELECT COLUMN NAME  FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    YourTableName

User · Answer

Since SysColumns is deprecated  use Sys All Columns   Select    ObjectName         Object Name Object ID     T Name    C      T     From               Sys All Columns C   Inner Join Sys Types       T  On T User Type Id   C User Type Id   Where  Object ID    Object ID  Sys Server Permissions     --Order By Name Asc     Select   From Sys Types will yield  user type id   ID of the type  This is unique within the database  For system data types  user type id   system type id

User · Answer

This is the easiest way  exec sp columns  tablename

User · Answer

One method is to query syscolumns   select    syscolumns name as  Column      syscolumns xusertype as  Type      sysobjects xtype as  Objtype  from     sysobjects  inner join     syscolumns on sysobjects id   syscolumns id where sysobjects xtype    u  and   sysobjects name    MyTableName  order by syscolumns name

User · Answer

While  Gulzar Nazim s answer is great  it is probably easier to include the database name in the query  which could be achieved by the following SQL   SELECT COLUMN NAME    FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    you-table-name  AND TABLE CATALOG  your-database-name

User · Answer

I m not sure if the syscolumns colid value is the same as the  ORDINAL POSITION  value returned as part of sp columns  but in what follows I am using it that way - hope I m not misinforming     Here s a slight variation on some of the other answers I ve found - I use this because the  position  or order of the column in the table is important in my application - I basically need to know  What is column  n  called    sp columns returns a whole bunch of extraneous stuff  and I m handier with a select than T-SQL functions  so I went this route   select       syscolumns name     syscolumns colid     from        sysobjects  syscolumns   where    sysobjects id   syscolumns id and      sysobjects xtype    u  and      sysobjects name     lt YOUR TABLE gt    order by syscolumns colid

User · Answer

You can use the below code to print all column names  You can also modify the code to print other details in whichever format u like      declare  Result varchar max                              select  Result  Result    ColumnName                             from                               select                     replace col name            ColumnName                      column id ColumnId                 from sys columns col                     join sys types typ on                         col system type id   typ system type id AND col user type id   typ user type id                 where object id   object id  tblPracticeTestSections                 t             order by ColumnId             print  Result   Output  column1 column2 column3 column4   To use the same code to print the table and its column name as C  class use the below code       declare  TableName sysname     lt EnterTableName gt       declare  Result varchar max     public class      TableName                 select  Result    Result             public static string     ColumnName       get   return    ColumnName                   from               select             replace col name            ColumnName              column id ColumnId         from sys columns col             join sys types typ on                 col system type id   typ system type id AND col user type id   typ user type id         where object id   object id  TableName        t     order by ColumnId      set  Result    Result                  print  Result   Output    public class tblPracticeTestSections       public static string column1   get   return  column1           public static string column2  get   return  column2           public static string column3  get   return  column3           public static string column4  get   return  column4

User · Answer

IF you are working with postgresql there is a possibility that more than one schema may have table with same name in that case apply the below query  SELECT column name  data type  FROM information schema columns WHERE table name    your table name  AND table schema    your schema name

User · Answer

set fmtonly on select   from yourTable

User · Answer

Not sure if there is an easier way in 2008 version   USE  Database Name  SELECT COLUMN NAME    FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    YourTableName  AND TABLE SCHEMA  YourSchemaName

[sql] How do you return the column names of a table?

Examples related to sql

Examples related to sql-server

Examples related to tsql