Hello, I have table with many columns and would like to do select like Select * from table and would like that result of query is alphabetically by column names Is this possible?

Regards

Tomaž

asked 22 Aug, 03:44

Tomaz's gravatar image

Tomaz
1166713
accept rate: 0%

2

Will you accept an answer, similar to this one https://stackoverflow.com/a/10498945 where the SQL query will be built at runtime via the stored procedure?

The idea is quite simple, you get all columns from the DB, sort them, create a string and then execute it as an SQL statement.

(22 Aug, 04:21) Vlad

ALTER PROCEDURE "DBA"."SelectWithColumnsOrdered"(in @table_name char(100))
BEGIN
  declare @sql long varchar ;

  select list(cname, ', ' order by cname)
    into @sql
    from sys.syscolumns
   where tname = @table_name ;

  set @sql = 'select ' || @sql || ' from ' || @table_name ;

  execute immediate with result set on @sql  ;
END
permanent link

answered 23 Aug, 05:56

Dmitri's gravatar image

Dmitri
1.6k41133
accept rate: 11%

FWIW, with v17, indirect identifiers often help to omit execute immediate calls - however, I don't know whether they could also be of help for cases like this with a variable number of identifiers (here columns)... - just in case someone has an idea...

(23 Aug, 08:07) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×64

question asked: 22 Aug, 03:44

question was seen: 117 times

last updated: 23 Aug, 08:07