How to change column order in a table using sql query in sql server 2005

Question

How to change column order in a table using SQL query in SQL Server 2005   I want to rearrange column order in a table using SQL query

User · Answer

You can of course change the order of the columns in a sql statement  However if you want to abstract tables  physical column order  you can create a view  i e  CREATE TABLE myTable      a int NULL      b varchar 50  NULL      c datetime NULL      CREATE VIEW vw myTable AS SELECT c  a  b   FROM myTable   select   from myTable  a  b  c -  -  -  select   from vw myTable c  a  b -  -  -

User · Answer

This is similar to the question on ordering the records in the result of a query    and typically no one likes the formally correct answer  -   So here it goes    as per SQL standard  the columns in a table are not  ordered  as a result  a select   does not force the columns to be returned in a particular order typically  each RDBMS has a kind of  default  order  usually the order that the columns were added to the table  either in the create table  or in thealter table add   statements therefore  if you rely on the order of columns  because you are using the results of a query to poulate some other datastructure from the position of the columns   explicitly list the columns in the order you want them

User · Answer

according to http   msdn microsoft com en-us library aa337556 aspx      This task is not supported using Transact-SQL statements    Well  it can be done  using create  copy   drop  rename  as answered by komma8 komma1  Or you can use SQL Server Management Studio        In Object Explorer  right-click the table with columns you want to reorder and click Design  Modify in ver  2005 SP1 or earlier     Select the box to the left of the column name that you want to reorder   You can select multiple columns by holding the  shift  or   the  ctrl  keys on your keyboard     Drag the column s  to another location within the table       Then click save  This method actually drops and recreates the table  so some errors might occur   If Change Tracking option is enabled for the database and the table  you shouldn t use this method   If it is disabled  the Prevent saving changes that require the table re-creation option should be cleared in Tools menu   Options   Designers  otherwise  Saving changes is not permitted  error will occur     Disabling the Prevent saving changes that require the table re-creation option is strongly advised against by Microsoft  as it leads to the existing change tracking information being deleted when the table is re-created  so you should never disable this option if Change Tracking is enabled    Problems may also arise during primary and foreign key creation   If any of the above errors occurs  saving fails which leaves you with the original column order

User · Answer

In order to have a specific column order You need to select column by column in the order You wish  Selection order dictates how columns will be ordered in output

User · Answer

You cannot  The column order is just a  cosmetic  thing we humans care about - to SQL Server  it s almost always absolutely irrelevant   What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLE command  copying over the data from the old table  and then dropping it   There is no SQL command to define the column ordering

User · Answer

You have to explicitly list the fields in the order you want them to be returned instead of using   for the  default  order   original query   select   from foobar   returns  foo bar --- ---   1   2   now write  select bar  foo from foobar  bar foo --- ---   2   1

User · Answer

You can do it by creating a new table  copy all the data over  drop the old table  then renaming the new one to replace the old one   You could also add new columns to the table  copy the column by column data over  drop the old columns  then rename new columns to match the old ones  A simple example below  http   sqlfiddle com   3 67af4 1  CREATE TABLE TestTable       Column1 INT      Column2 VARCHAR 255     GO  insert into TestTable values 1   Test1    insert into TestTable values 2   Test2    GO  select   from TestTable  GO  ALTER TABLE TestTable ADD Column2 NEW VARCHAR 255   ALTER TABLE TestTable ADD Column1 NEW INT  GO  update TestTable  set Column1 NEW   Column1       Column2 NEW   Column2  GO  ALTER TABLE TestTable DROP COLUMN Column1  ALTER TABLE TestTable DROP COLUMN Column2  GO  sp rename  TestTable Column1 NEW    Column1    COLUMN   GO sp rename  TestTable Column2 NEW    Column2    COLUMN   GO  select   from TestTable  GO

User · Answer

In SQLServer Management Studio   Tools -  Options -  Designers -  Table and Database Designers   Unselect  Prevent saving changes that require table re-creation     Then    right click the table you want to re-order the columns for  click  Design   Drag the columns to the order you want  finally  click save    SQLServer Management studio will drop the table and recreate it using the data

User · Answer

I suppose you want to add a new column in a specific position  You can create a new column by moving current columns to the right    --- --- ---    A   B   C    --- --- ---    Remove all affected indexes and foreign key references  Add a new column with the exact same data type like the last column and copy data there    --- --- --- ---    A   B   C   C    --- --- --- ---                    Change data type of the third column to the same type like the previous column and copy data there     --- --- --- ---    A   B   B   C     --- --- --- ---                Rename columns accordingly  recreate removed indexes and foreign key references    --- --- --- ---    A   D   B   C     --- --- --- ---    Change data type of the second colum   Keep in mind that the column order is just a  cosmetic  thing like marc s said

User · Answer

You can change this using SQL query  Here is sql query to change the sequence of column   ALTER TABLE table name  CHANGE COLUMN  column1   column1  INT 11  NOT NULL COMMENT    AFTER  column2

User · Answer

Use  SELECT   FROM TABLE1   which displays the default column order of the table   If you want to change the order of the columns   Specify the column name to display correspondingly  SELECT COLUMN1  COLUMN5  COLUMN4  COLUMN3  COULMN2 FROM TABLE1

User · Answer

Sql server internally build the script  It create a temporary table with new changes and copy the data and drop current table then recreate the table insert from temp table  I find it from  Generate Change script  option ssms 2014  Script like this  From Here  How to change column order in a table using sql query  BEGIN TRANSACTION SET QUOTED IDENTIFIER ON SET ARITHABORT ON SET NUMERIC ROUNDABORT OFF SET CONCAT NULL YIELDS NULL ON SET ANSI NULLS ON SET ANSI PADDING ON SET ANSI WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo Tmp emps           id int NULL      ename varchar 20  NULL        ON  PRIMARY  GO ALTER TABLE dbo Tmp emps SET  LOCK ESCALATION   TABLE  GO IF EXISTS SELECT   FROM dbo emps       EXEC  INSERT INTO dbo Tmp emps  id  ename          SELECT id  ename FROM dbo emps WITH  HOLDLOCK TABLOCKX    GO DROP TABLE dbo emps GO EXECUTE sp rename N dbo Tmp emps   N emps    OBJECT   GO COMMIT

User · Answer

Try this command   alter table students modify age int 5  first     This will change the position of age to the first position

User · Answer

alter table name modify columnname int 5  first   will bring the column to first  alter table name modify columnname int 5  after  tablename

User · Answer

At the end of the day  you simply cannot do this in MS SQL  I recently created tables on the go  application startup  using a stored Procedure that reads from a lookup table  When I created a view that combined these with another table I had manually created earlier one  same schema  with data   It failed - simply because I was using   Select   UNION Select     for the view  At the same time  if I use only those created through the stored procedure  I am successful   In conclusion  If there is any application which depends on the order of column it is really not good programming and will for sure create problems in the future  Columns should  feel  free to be anywhere and be used for any data process  INSERT  UPDATE  SELECT

User · Answer

you can use indexing   After indexing  if select   from XXXX results should be as per the index  But only result set   not structrue of Table

User · Answer

In SQLServer Management Studio   Tools -  Options -  Designers -  Table and Database Designers  Unselect Prevent saving changes that require table re-creation   Now you can reorder the table

User · Answer

You can achieve it with these steps    remove all foreign keys and primary key of the original table  rename the original table  using CTAS create the original table in the order you want  drop the old table  apply all constraints back to the original table

User · Answer

If the columns to be reordered have recently been created and are empty  then the columns can be deleted and re-added in the correct order   This happened to me  extending a database manually to add new functionality  and I had missed a column out  and when I added it  the sequence was incorrect   After finding no adequate solution here I simply corrected the table using the following kind of commands   ALTER TABLE  tablename  DROP COLUMN  columnname   ALTER TABLE  tablename  ADD columnname columntype    Note  only do this if you don t have data in the columns you are dropping   People have said that column order does not matter  I regularly use SQL Server Management Studio  generate scripts  to create a text version of a database s schema  To effectively version control these scripts  git  and to compare them  WinMerge   it is imperative that the output from compatible databases is the same  and the differences highlighted are genuine database differences   Column order does matter  but just to some people  not to everyone

User · Answer

If your table has enough columns then you can try this  First create a new table with preferred order of columns       create table new as select column1 column2 column3     columnN from table name    Now drop the table using drop command      drop table table name    now rename the newly created table to your old table name       rename new to table name    now select the table  you have your columns rearranged as you preferred before       select   from table name

User · Answer

Example  Change position of field priority after field price in table status   ALTER TABLE  status  CHANGE  priority   priority  INT 11  NULL DEFAULT NULL AFTER  price

[sql] How to change column order in a table using sql query in sql server 2005?

Examples related to sql

Examples related to sql-server-2005