0

Is it possible to retrieve the list of the name of the columns of a table in a SQL Server database?

Revious
  • 7,816
  • 31
  • 98
  • 147
  • possible duplicate of [get basic SQL Server table structure information](http://stackoverflow.com/questions/14819994/get-basic-sql-server-table-structure-information) – David Mar 17 '14 at 14:44
  • I've found also this answer: http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server – Revious Mar 17 '14 at 14:47

4 Answers4

1

This should work with any version of SQL Server.

SELECT c.COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'schemaName'
AND c.TABLE_NAME = 'tableName'
ORDER BY c.ORDINAL_POSITION
Dave Mason
  • 4,746
  • 2
  • 23
  • 24
1

You can press alt+f1 while the table name is selected, this does the same thing as:

sp_help @objname= 'table_name'

Luuk
  • 145
  • 6
1

Try this:

SELECT 
    c.name 'Column Name',

FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

OR THIS:

SELECT SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
WHERE t.name = 'YourTableName'
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
ORDER BY schema_name, table_name;
Van Gogh
  • 465
  • 1
  • 7
  • 8
1

Beginning with SQL Server 2005 (and up through SQL Server 2012, as of the writing of this answer), the sp_columns stored procedure can provide you with the list of columns (plus a bunch of other stuff too!) for a specific table. The returned result set will include the COLUMN_NAME column, which has what you are looking for.

To use it, run the following statement in your database:

EXECUTE sp_columns @table_name='YourTableName'

Microsoft's online help for sp_columns can be found here.

László Koller
  • 1,139
  • 6
  • 15