Change ordinal position of the Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change ordinal position of the Column

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-06-18 : 01:02:24

1 have a table with 74 colun.But due to some deletion i have the ordinal postions as
1
2
3
.
.
69
70
72
73
74
76

what i wants is to change the ordinal from 72 to 71,73 to 72,74 to 73,76 to 74

SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
-- handle insert case here
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.

I needed it as i have a trigger whihc is adding value in a log table on update but due to this problem it fails for certain column

Trgger is solme thing like

WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
BEGIN
IF @Type not in ('I')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @field -1
--print('fieldname = '+@fieldname)
SELECT @sql = 'insert Audit (ActionTypeID, RowID, TableName, PK, FieldName, OldValue, NewValue, UpdateDate,UserID, UserName, UserType)'
SELECT @sql = @sql + ' select convert(bigint,' + @ActionTypeID + ')'
SELECT @sql = @sql + ',' + @RowID
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',' + @UserID
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @UserType + ''''
SELECT @sql = @sql + ' from #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
--print('@sq=====sdfdfsfsdf')--sha
--print(@sql)--sha
EXEC (@sql)
END
END
END

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 01:21:58

Any specific reason why you want ordinal numbers to be changed? You can always retrieve the data from columns in the order you want by specfying in select list.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-06-18 : 01:39:06

If you can take a look at the trigger you can understand it.
Problem is that i can't delete the table.[there are number of reason]

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-18 : 01:47:57

quote:
Originally posted by kamii47

If you can take a look at the trigger you can understand it...

That is very optimistic.

CODO ERGO SUM

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-06-18 : 02:44:31

I have tried following
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE

UPDATE sc
SET sc.colorder =
CASE
WHEN sc.colorder = 72 THEN 71
WHEN sc.colorder = 73 THEN 72
WHEN sc.colorder = 74 THEN 73
WHEN sc.colorder = 76 THEN 74
ELSE sc.colorder
END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'student'
GO

but got the error
Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-18 : 07:47:05

Why do you need to change the ordinal positions? I can't find a single reason why anyone would need to do this...

--
Lumbago

pootle_flump

1064 Posts

Posted - 2008-06-18 : 08:14:19

Kamran - in set theory the order of attributes in a relation has no meaning. It should be the same when you consider columns in a table. Be assured that this is a bad idea.

Could you explain the underlying business reason for why you are looking to change the ordinal positions of the columns? Other than for its own sake, what problem would this solve?

Tabas
Starting Member

1 Post

Posted - 2009-05-29 : 14:11:32

I'm attempting to change the order position, because my application allows to create tables on the fly to END user. Later, this table is used to capture data like a template.. but.. sometimes.. the field order is required.. because on the practice.. the end user can see that info in field 'Z' is more important that info in field 'C'.

Other alternative is create a secundary table with the fields in your table with a position field...and use this secundary table in your process.

Regards
César F. Qüeb Montejo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-29 : 15:32:16

Well, I can tell you, like pretty much anyone that is going to offer help, that this is a very bad idea.

What I hear is that certain columns have a higher weight than others. If that is the case you should design your application accordingly. However, I'm more than happy to had out rope.. so..

One option is to create a new table with the correct ordinal positions. Copy the data from the original table then drop the origianl table and rename the new table. There are other ways. If you do some searching on this site you can find some other alternatives (all equally bad).

plmyfinger
Starting Member

1 Post

Posted - 2012-06-19 : 18:00:35

I actually have a use case for changing the ordinal position of the columns.
I have an existing view that returs a long list of columns from a long list of joins. Ther are some cast statements in line, some case statements, and even a sub-query or two.
This view is customized for each customer, so there are many variations of the same view.

Next, Why do you need the ordinal position to be the same?
Well, I have to swap the result into a partitioned table. Since chaning out 20+ views will be very time consuming (these have over 1000 lines each) and the likelyhood of human error will be significant enough, I want to avoid this.
The solution I have in mind is to use a script to realign the temp table created from the view to have its ordinal positions match the partitioned table.
Any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources