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

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 *`.

Just run this command



EXEC sp_columns 'Your Table Name'

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

SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

`TABLE_NAME` is your table

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 <kbd>ALT</kbd>+<kbd>F1</kbd>.

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 [

[To see links please register here]

][1]


[1]:

[To see links please register here]

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.

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


This sample query:

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


Can be made over all these DB objects:

- [CHECK_CONSTRAINTS][2]
- [COLUMN_DOMAIN_USAGE][3]
- [COLUMN_PRIVILEGES][4]
- [COLUMNS][5]
- [CONSTRAINT_COLUMN_USAGE][6]
- [CONSTRAINT_TABLE_USAGE][7]
- [DOMAIN_CONSTRAINTS][8]
- [DOMAINS][9]
- [KEY_COLUMN_USAGE][10]
- [PARAMETERS][11]
- [REFERENTIAL_CONSTRAINTS][12]
- [ROUTINES][13]
- [ROUTINE_COLUMNS][14]
- [SCHEMATA][15]
- [TABLE_CONSTRAINTS][16]
- [TABLE_PRIVILEGES][17]
- [TABLES][18]
- [VIEW_COLUMN_USAGE][19]
- [VIEW_TABLE_USAGE][20]
- [VIEWS][21]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

[6]:

[To see links please register here]

[7]:

[To see links please register here]

[8]:

[To see links please register here]

[9]:

[To see links please register here]

[10]:

[To see links please register here]

[11]:

[To see links please register here]

[12]:

[To see links please register here]

[13]:

[To see links please register here]

[14]:

[To see links please register here]

[15]:

[To see links please register here]

[16]:

[To see links please register here]

[17]:

[To see links please register here]

[18]:

[To see links please register here]

[19]:

[To see links please register here]

[20]:

[To see links please register here]

[21]:

[To see links please register here]

SELECT TOP (0) [toID]
,[sourceID]
,[name]
,[address]
FROM [ReportDatabase].[Ticket].[To]

Simple and doesnt require any sys tables

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

Some SQL Generating SQL:
```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 ;

```