Hi All, i just wants to move a column from its 15th position to 2nd position in table using SQL(query analyzer NOT Entr.Mangr).
Reason : COLUMNS_UPDATED() function will only check the updated status upto 8th column in a table(as per my information) and i have to check this column.

Please updated,
with thanks .....kasana:)

You can't use alter table for this.
You have to drop the table and recreate it.

In general, column position should not affect the your database design/programming.

You *might* be able to update the information in the syscolumns system table to change the column order. This is absolutely not recommended.

If you absolutely have to change column positions, either re-create the table structure and import the data, or create a view with the proper column positions, and use the view instead.

You use Columns_Updated() in the trigger not for moving column position

kasanar,

If you are looking for help on moving column from its position to other, as per my understanding, it could be done using alter table command. Else you can run a select to have this column placed anywhere in position as you wished. But I dont see that would help your cause.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Why do you think COLUMNS_UPDATED() is limited to only 8 columns?

From BOL:

"The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost."

This means that column 1 in the table is the leftmost bit of the first byte, column 9 is the leftmost bit in the second byte, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,
Can we change column position using ALTER TABLE? Correct me If I am wrong.

I don't think so. The only way I know to "renumber" columns is to create a new table with the columns in the new order and copy the data contents from the original. If you use EM to move columns around in a table, look at the script that it generates and you'll see that it does it this way.

Changing the column "number" means that the data must move around in the data pages. The data is stored sequentially in column number order within the data row (var datatypes notwithstanding), and I know no way to change that short of making a new table...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks Jeff, it is done as for 3rd column :
if (substring(columns_updated(),1,1) & 4 ) =4
Kasana