| Author |
 Topic  |
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
 Posted - 06/18/2008 : 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
India
47173 Posts |
Posted - 06/18/2008 : 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
335 Posts |
Posted - 06/18/2008 : 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)
USA
6997 Posts |
Posted - 06/18/2008 : 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
335 Posts |
Posted - 06/18/2008 : 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
Norway
3241 Posts |
Posted - 06/18/2008 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 06/18/2008 : 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 Posts |
Posted - 05/29/2009 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 05/29/2009 : 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 Posts |
Posted - 06/19/2012 : 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? |
|
|
| |
Topic |
|
Change ordinal position of the Column
