6

In SQL Server, we get the table description using below command:

Sp_help TableName

When it displays all column names in a random order. Is there a way If I want to get all column names alphabetically sorted in some order (Descending or Ascending)?

This will help me to have a quick look at the table to see what all columns are present and whether a specific column is present in the table or not.

Trupti J
  • 512
  • 1
  • 4
  • 16
  • 1
    Possible duplicate of [How do you return the column names of a table?](https://stackoverflow.com/questions/600446/how-do-you-return-the-column-names-of-a-table) – Sam M Aug 30 '18 at 05:27

3 Answers3

8

You may get the List of Column from the System View which is INFORMATION_SCHEMA.COLUMNS. You can do a Select on the View and Filter by Table there order the List based on any of your desired values as mentioned below: 

SELECT  
    *
    FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME ='YourTableName'
        ORDER BY COLUMN_NAME ASC
Trupti J
  • 512
  • 1
  • 4
  • 16
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
3

There is one more query to achieve this:

SELECT  * 
        FROM    sys.COLUMNS where object_id = OBJECT_ID('yourtablename') 
        ORDER   By NAME
Trupti J
  • 512
  • 1
  • 4
  • 16
0

EDIT: Oops, other answer just beat me, and mine is in table order not alphabetical order. But look at Information_schema and you can do whatever you want.

Use information_schema.

select column_name from information_schema.columns where table_name='yourtable' order by ordinal_position
TomC
  • 2,759
  • 1
  • 7
  • 16