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

Question

I want to query the name of all columns of a table  I found how to do this in   Oracle MySQL PostgreSQL  But I also need to know  how can this be done in Microsoft SQL Server  2008 in my case

User · Answer

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

User · Answer

SELECT name FROM sys columns WHERE object id   OBJECT ID  TABLE NAME     TABLE NAME is your table

User · Answer

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

User · Answer

SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    name of your table

User · Answer

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

User · Answer

You can try this This gives all the column names with their respective data types   desc  lt TABLE NAME gt

User · Answer

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

User · Answer

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      CHECK CONSTRAINTS   COLUMN DOMAIN USAGE COLUMN PRIVILEGES   COLUMNS   CONSTRAINT COLUMN USAGE   CONSTRAINT TABLE USAGE   DOMAIN CONSTRAINTS   DOMAINS   KEY COLUMN USAGE   PARAMETERS   REFERENTIAL CONSTRAINTS   ROUTINES   ROUTINE COLUMNS   SCHEMATA   TABLE CONSTRAINTS   TABLE PRIVILEGES   TABLES   VIEW COLUMN USAGE   VIEW TABLE USAGE   VIEWS

User · Answer

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

User · Answer

By using this query you get the answer   select Column name  from Information schema columns  where Table name like  table name

User · Answer

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

User · Answer

SELECT TOP  0   toID          sourceID          name          address    FROM  ReportDatabase   Ticket   To    Simple and doesnt require any sys tables

User · Answer

You can use sp help in SQL Server 2008   sp help  lt table name gt     Keyboard shortcut for the above command  select table name  i e highlight it  and press ALT F1

User · Answer

select   from INFORMATION SCHEMA COLUMNS where TABLE NAME  tableName    This is better than getting from sys columns because it shows DATA TYPE directly

User · Answer

Just run this command   EXEC sp columns  Your Table Name

User · Answer

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

User · Answer

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

User · Answer

you can use this query   SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME like N   ColumnName    and TABLE NAME   N  TableName

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql