Select statement: ordering by column name.

Lost User

PHS241 wrote:

Is it possible to select this table's records and list them in column order animal, mineral, vegetable?

No, unless you write a small macro that extends your IDE to do so.

PHS241 wrote:

I have some tables that have an awful lot of columns

How much is "awfull"? Are you sure the design is correct?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Septimus Hedgehog

Eddy, the table predates my time at the company. It's a financial type of table and has about 58 columns. I'm sure there are bigger tables out there in the world. It would have been a nice to do if it was a quick shoot-from-the-hip statement clause but I can live with the existing ordinal position column order.

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.

Mycroft Holmes

I once wrote a script that went through the database and created views for each table sorting the columns alphabetically. I would then use the view when I needed the A suffix format.

Never underestimate the power of human stupidity RAH

Septimus Hedgehog

I see your thinking. That's something to bear in mind as well. I only really need this as I'm investigating some production problems that involve a number of tables and I don't think I'd be allowed to create anything new on the database.

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.

GuyThiebaut

This will do the trick for one table. Just build another cursor around this cursor to do it by table. (my Jedi senses tell that I am going to get flamed by someone for suggesting a cursor)

DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable'
ORDER BY column_name

declare @qry as nvarchar(max)
set @qry = 'select '

DECLARE @col nvarchar(max)

OPEN curs
FETCH NEXT FROM curs into @col
WHILE @@FETCH_STATUS = 0
BEGIN
set @qry += '['+@col + '],'
FETCH NEXT FROM curs into @col
END

CLOSE curs
DEALLOCATE curs

set @qry = substring(@qry,1,len(@qry)-1) + ' from yourTable'

exec(@qry)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

David Mujica

Maybe you could build a stored procedure based on the following SQL which gives you the column names in alphabetical order:

Use [YourDatabase]
GO

SELECT SC.NAME FROM SYS.objects SO, SYS.columns SC
WHERE SO.TYPE = 'U' AND SO.name LIKE '[yourTableName]'
AND SO.object_id = SC.object_id
ORDER BY SC.name

You could use this to create a view or create a Dynamic SQL Statement where you pass in your "where clause". Hope this helps. :thumbsup:

Tim Carmichael

In regards to using cursors, yes, they work... mostly. However, I've had issues in the past where they didn't work, so, as ugly as it is, I switched to WHILE loops... Ugly, but it worked consistenly. Tim

GuyThiebaut

You're right - here's the code for all tables using the while version:

set rowcount 1

declare @table as nvarchar(max)
declare @tbl_rowcount as int
declare @col_rowcount as int
declare @col nvarchar(max)
declare @qry as nvarchar(max)

select @table = table_name
from information_schema.tables
order by table_name

select @tbl_rowcount = @@rowcount

while (@tbl_rowcount != 0)
begin

set @qry = 'select ' + '''' + @table + '''' + ' as tableName, '

select @col = column_name
from information_schema.columns
where table_name = @table
order by column_name

select @col_rowcount = @@rowcount

while (@col_rowcount != 0)
begin

  set @qry += '\['+@col + '\],'
  
  select @col = column\_name
  from information\_schema.columns
  where table\_name = @table
  and column\_name > @col
  order by column\_name
  
  select @col\_rowcount = @@rowcount

end

set @qry = substring(@qry,1,len(@qry)-1) + ' from ' + @table
set rowcount 0
exec(@qry)
set rowcount 1

select @table = table_name
from information_schema.tables
where table_name > @table
order by table_name

set @tbl_rowcount = @@rowcount

end

set rowcount 0

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Ralph D Wilson II

Maybe I missed something in reading through the thread but I'll ask anyway. ;-) What RDBMS are you using? If it is SQL Server (or probably most others) you can create a query that will generate a SELECT statement from the table and column information in the system tables (in SQL Server, sys.tables and sys.columns). Once that is generated (and a DECLAREd variable set to that varchar string, you can execute the r results.

Septimus Hedgehog

Good morning, Ralph. The database we use is SQL Server 2008. Is the idea you have in mind different to the other suggestions? If it is do you think you could paste an example for me/us?

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.

Ralph D Wilson II

Th following could be implemented as a Sotred Procedure or simply executed as is in a query:

USE {enter your desired database name here};

DECLARE @NameOfTable VarChar(128);
SET @NameOfTable = '{enter your desired table name here}';

DECLARE @SQLStatement VarChar(8000);

SET @SQLStatement = 'SELECT ''' + @NameOfTable + ''' AS TableName';

PRINT @SQLStatement;

EXEC(@SQLStatement);

WITH TableColumns_CTE
AS
(
SELECT T.name AS TableName
,C.name AS ColumnName
,ROW_NUMBER() OVER (PARTITION BY T.name ORDER BY T.name, C.name) Seq
FROM sys.tables T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
AND T.name = @NameOfTable
)

SELECT @SQLStatement = @SQLStatement + ', ' + ColumnName
FROM TableColumns_CTE;

SELECT @SQLStatement = @SQLStatement + ' FROM ' + @NameOfTable + ';';

PRINT @SQLStatement;

EXEC(@SQLStatement);

Septimus Hedgehog

Ralph, thank you for that. I appreciate it and will try it at the office tomorrow.:thumbsup:

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.

Ralph D Wilson II

Did that wok for you?

Septimus Hedgehog

Ralph, I completely forgot to try it. I just c&p into ssms but it didn't run clean and throws some errors. I need to schedule some time to look at it, but off the shelf, I couldn't run it even though I replaced the database name and table with correct values.

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.