SQL 按字母顺序列出所有列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4075800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Listing all column names alphabetically
提问by Shamim Hafiz
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?
可以用 SQL 做到这一点吗？
采纳答案by paxdiablo
Yes, and no :-)
是的，也不是 :-)
SQL itself doesn't care what order the columns come out in but, if you were to use:
SQL 本身并不关心列出现的顺序，但是，如果您要使用：
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).
你会发现它们可能是按这个顺序出现的（尽管我不确定 SQL 标准是否要求这样做）。
Now you may not wantto 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:
您还可以使用 DBMS 数据定义表来动态构建查询。这是不可移植的，但大多数 DBMS 提供这些表（例如 DB/2 的SYSIBM.SYSCOLUMNS），您可以从那里以有序的方式选择列名。就像是：
select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;
Then you use the results of thatquery 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.
但是，您确实应该仔细检查所有选择的查询*- 在绝大多数情况下，这是不必要的且效率低下的。数据的表示可能应该由表示层完成，而不是 DBMS 本身——应该让 DBMS 以尽可能有效的方式返回数据。
回答by Maykol Rypka
This generates a query with all columns ordered alphabetically in the select statement.
这将生成一个查询，其中所有列在 select 语句中按字母顺序排列。
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)
回答by gbn
- 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
- 如果没有动态 SQL，就无法自动执行此操作。
SELECT *不推荐并且不会对列名进行排序- 你必须明确地做
SELECT age, name, sex FROM
At the SQL level, it does not matter. Not does it matter to any client code object-
在 SQL 级别，这无关紧要。与任何客户端代码对象无关 -
If it's important, then sort when you present the data to the client.
如果它很重要，那么在向客户展示数据时进行排序。
Sorry, it just is that way...
对不起，就是这样……
回答by onedaywhen
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.
SQL-92 标准规定，在使用SELECT *列时，按其在表中的序号位置的升序进行引用。相关部分是 4.8（列）和 7.9（查询规范）。我不知道标准的任何供应商扩展允许以任何其他顺序返回列，可能是因为SELECT *通常不鼓励使用。
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 FROMclause. 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.
您可以使用 SQL DDL 来确保列的序号位置与所需的字母顺序匹配。但是，这只会在引用FROM子句中的单个表时以您想要的方式工作。如果引用了两个表，SELECT *将返回第一个表中按顺序位置顺序的列，然后是按顺序位置的第二个表的列，因此完整结果集的列可能不是按字母顺序排列的。
回答by Kel
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.
列将按照您在查询中指定的顺序显示。
回答by Pamuleti Pullagura
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.
它将按字母顺序显示表格的所有列。
回答by Lewis Hepburn
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.
另一种方法是通过 SQL 过程更改表，按字母顺序排列所有列。我为几个表格创建了一个，其中我的用户更喜欢字母布局，同时仍然使用简化的SELECT *语句。
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.
这段代码应该首先排列我的索引，然后组织来自 AZ 的所有其他列。您的实例可能会有所不同，但这是一个很好的起点。
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!
希望这可以帮助！
回答by andrew pate
If you just trying to find a column, on SQL Server.
如果您只是想在 SQL Server 上查找列。
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableThatCouldMaybeNeedNormalising'
order by COLUMN_NAME
