May 15, 2002 at 9:51 am
Is there a way to programmatically sort all the columns in a table. What I want to do is sort, in ascending order, all of the fields that are defined in my table. It is the column names that I want to sort, not the data.
May 15, 2002 at 9:53 am
I don't believe so...never thought about doing it personally.
If the columns need to come out in the correct alphabetic order, you could alter the select statement to call the columns in order, or simply recreate the table defining the columns in the correct order.
Clive
Clive Strong
May 15, 2002 at 10:12 am
Can you give me an example please? You can use a dynamic SQL statement to build the query and do a SELECT with column the columsn in order. SOmething like this
DECLARE @SQLState VARCHAR(4000)
DECLARE @ColNames VARCHAR(2000)
SET @ColNames = NULL
SELECT @ColNames = (CASE WHEN @ColNames IS NULL THEN '[' + [name] + ']' ELSE @ColNames + ', ' + '[' + [name] + ']' END) FROM syscolumns WHERE [id] = OBJECT_ID('urTblHere') ORDER BY [name]
SET @SQLState = 'SELECT ' + @ColNames + ' FROM urTblHere'
EXEC(@SQLState)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 10:46 am
Do you want to change columns order in the table?
If you run a query
select name,colid from syscolumns where id = object_id("table_name")
and insert into temptable with identity column you can make changes to colid and set them in this order ( if that what you want)
May 15, 2002 at 12:35 pm
If you just want to sort the column order then use Antares686' example, if you want to rewite the table structure.
You have to move everything into a temp table recreate the table and move the data back into the table.
In this case you can modify Antares686' sample to include datatypes ets, and make a "Create table" statement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply