Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
select * from employee

--now these are the columns in the table
--O/P
HCLDEP SAPID NAME GENDER
1 514 ANURAG MALE
2 325 SIBA MALE
3 888 MUKUA MALE
4 888 RITA FEMALE
3 243 BIKASH MALE
3 711 AKASH MALE
2 500 RUCHI FEMALE
2 750 PRACHI FEMALE
2 620 PUSHPA FEMALE

--I am adding a column
alter table employee
add project varchar(50)

Select * from employee

--Now the column gets added at the end

----------------------------Changing ordinal Position------------------------------------------
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='employee'


--I am trying to change the ordinal position of the new column to 2nd column

UPDATE INFORMATION_SCHEMA.COLUMNS
SET ORDINAL_POSITION=2 WHERE TABLE_NAME='employee' AND COLUMN_NAME='project'


But I am getting the below error
Ad hoc updates to system catalogs are not allowed.


how to rectify this error ?
Posted
Comments
ZurdoDev 26-Aug-13 13:11pm    
Out of curiosity, why are you trying to change the order?
anurag19289 26-Aug-13 13:14pm    
In one of my table i have around 20 columns. The 18th number column is most needed for me. Everytime i have to write in this way.
select status,* from tbl_route_detail where route_id=23324 and userid='24524524'

so i thought if i can change the ordinal position...then directly i can see that column
Sergey Alexandrovich Kryukov 26-Aug-13 13:54pm    
The error says: "don't do it", so don't do it. :-)
—SA
anurag19289 26-Aug-13 14:02pm    
ok....
joshrduncan2012 26-Aug-13 14:44pm    
I agree. Can't make changes to the database schema, essentially.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900