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.