SQL Listing all column names alphabetically

Question

I know that

SELECT * FROM Table

will list all columns in the table, but I am interested in listing the columns in alphabetical order.

Say, I have three columns, "name", "age" and "sex".

I want the columns organized in the format

|age| |name| |sex|

Is it possible to do this with SQL?

Answer 1

This generates a query with all columns ordered alphabetically in the select statement.

DECLARE @QUERY VARCHAR(2000)
DECLARE @TABLENAME VARCHAR(50) = '<YOU_TABLE>'

SET @QUERY = 'SELECT '
SELECT @QUERY = @QUERY + Column_name + ', 
'
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = @TABLENAME
 ORDER BY Column_name

SET @QUERY =  LEFT(@QUERY, LEN(@QUERY) - 4) + ' 
FROM '+ @TABLENAME

PRINT @QUERY
EXEC(@QUERY)

Answer 2

Yes, and no :-)

SQL itself doesn't care what order the columns come out in but, if you were to use:

select age, name, sex from ...

you'd find that they probably came out in that order (though I'm not sure SQL standards mandate this).

Now you may not want to do that but sometimes life isn't fair :-)

You also have the other possibility of using the DBMS data definition tables to dynamically construct a query. This is non-portable but most DBMS' supply these table (such as DB/2's SYSIBM.SYSCOLUMNS) and you can select the column names from there in an ordered fashion. Something like:

select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;

Then you use the results of that query to construct the real query:

query1 = "select column_name from sysibm.syscolumns" +
         " where owner = 'pax' and table_name = 'movies'" +
         " order by column_name"
rs = exec(query1)
query2 = "select"
sep = " "
foreach colm in rs:
    query2 += sep + colm["column_name"]
    sep = ", "
query2 += " from movies order by rating"
rs = exec(query2)
// Now you have the rs recordset with sorted columns.

However, you really should critically examine all queries that select * - in the vast majority of cases, it's unnecessary and inefficient. And presentation of the data is something that should probably be done by the presentation layer, not the DBMS itself - the DBMS should be left to return the data in as efficient a manner as possible.

Answer 3

• There is no way to do this automatically without dynamic SQL.
• SELECT * is not recommended and will not sort column names
• You'd have to explicitly do SELECT age, name, sex FROM

At the SQL level, it does not matter. Not does it matter to any client code object-

If it's important, then sort when you present the data to the client.

Sorry, it just is that way...

Answer 4

SQL-92 Standard specifies that when using SELECT * the columns are referenced in the ascending sequence of their ordinal position within the table. The relevant sections are 4.8 (columns) and 7.9 (query specification). I don't know of any vendor extensions to the Standard that would allow columns to be returned in any other order, probably because use of SELECT * is generally discouraged.

You can use SQL DDL to ensure that columns' ordinal positions match the desired alphabetical order. However, this will only work in the way you want when referening a sinlge table in the FROM clause. If two tables are referenced, SELECT * will return the columns from the first table in ordinal position order followed by the second table's columns in ordinal position, so the complete resultset's columns may not be in alphabetical order.

Answer 5

If you just trying to find a column, on SQL Server.

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableThatCouldMaybeNeedNormalising'
order by COLUMN_NAME

Answer 6

You may just specify columns you wish to select:

SELECT age, name, sex FROM Table

Columns will be shown in the same order as you specified them in query.

Answer 7

A different approach would be to arrange all columns alphabetically by altering the table via a SQL procedure. I created one for a couple of the tables in which my users prefer the alphabetic layout while still using the simplified SELECT * statement.

This code should arranged my index first and then organise all other columns from A-Z. It may be different for your instance but is a good starting point.

DELIMITER ;;

DROP PROCEDURE IF EXISTS ALPHABETISE_TABLE_COLUMNS;

CREATE PROCEDURE ALPHABETISE_TABLE_COLUMNS(IN database_name VARCHAR(64), IN table_name_string VARCHAR(64), IN index_name_string VARCHAR(64))

BEGIN

    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE col_name VARCHAR(30) DEFAULT "";
    DECLARE col_datatype VARCHAR(10) DEFAULT "";
    DECLARE previous_col VARCHAR(30) DEFAULT col_name;

    SELECT COUNT(*) 
    FROM 
        (SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = table_name_string) AS TEMP 
    INTO n;

    SET @Q= CONCAT('ALTER TABLE `',database_name,'`.`',table_name_string,'` CHANGE COLUMN `',index_name_string,'` `',index_name_string,'` BIGINT(20) NOT NULL FIRST');
    PREPARE exe FROM @Q;
    EXECUTE exe;
    DEALLOCATE PREPARE exe;

    SET n = n-1;
    SET i=1;

    WHILE i<n DO 


        SELECT COLUMN_NAME FROM 
            (SELECT COLUMN_NAME, @row_num:= @row_num + 1 as ind_rows 
            FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c 
            WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string 
            ORDER BY COLUMN_NAME ASC) as TEMP 
        WHERE ind_rows = i 
        INTO col_name;

        SELECT DATA_TYPE 
        FROM 
            (SELECT DATA_TYPE, @row_num:= @row_num + 1 as ind_rows 
            FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c 
            WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string 
            ORDER BY COLUMN_NAME ASC) as TEMP 
        WHERE ind_rows = i 
        INTO col_datatype;

        IF i = 1 THEN
            SET previous_col = index_name_string;
        ELSE
            SELECT COLUMN_NAME 
            FROM 
                (SELECT COLUMN_NAME, @row_num:= @row_num + 1 as ind_rows 
                FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c 
                WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string 
                ORDER BY COLUMN_NAME ASC) as TEMP 
            WHERE ind_rows = i-1
            INTO previous_col;
        END IF;

        IF col_datatype = 'varchar' THEN
            SET col_datatype = 'TEXT';
        END IF;

        select col_name, previous_col;
        IF col_name <> index_name_string OR index_name_string = '' THEN
            SET @Q= CONCAT('ALTER TABLE `',database_name,'`.`',table_name_string,'` CHANGE COLUMN `',col_name,'` `',col_name,'` ',col_datatype,' NULL DEFAULT NULL AFTER `',previous_col,'`');
            PREPARE exe FROM @Q;
            EXECUTE exe;
            DEALLOCATE PREPARE exe;
        END IF;
        SET i = i + 1;

    END WHILE;
END;
;;

DELIMITER ;

# NOTE: ASSUMES INDEX IS BIGINT(20), IF OTHER PLEASE ADAPT IN LINE 22 TO MEET DATATYPE
#
# CALL ALPHABETISE_TABLE_COLUMNS('database_name', 'column_name', 'index_name')

Hope this helps!

Answer 8

ORDER BY COLUMN_NAME ASC;

Note that the COLUMN_NAME function is used to refer to the column names of the table. This function is not available in all database systems, and it may not work as expected in some cases.

Answer 9

Yes. It is possible with the following command.

SELECT column_name FROM user_tab_cols WHERE table_name=UPPER('Your_Table_Name') order by column_name;

It will display all columns of your table in alphabetic order.