Post reply

Is there a way to reorder my table columns, I tried to do it unsuccessfully via system objects

  • August 25, 2008 at 9:05 pm

    #382404

    I have 160 tables in my SQL Server 2005 database.

    I need to put this column 'CreatedByID' as the last column for all the tables, so I thought I am able to do this by altering the value in the sys.columns's 'object_id' to give it the highest value (total column count + 1).

    But I am not successful. I got this error.

    "Ad hoc updates to system catalogs are not allowed"

    Is there an easier way to achieve this?

    select C.[object_id],T.[name],count(*) total_column

    into admin_table_column_count

    from sys.columns C inner join

    sys.tables T on C.object_id=T.object_id

    group by C.[object_id],T.[name]

    UPDATE sys.columns

    SET column_id = TC.total_column + 2

    from admin_table_column_count TC left outer join sys.columns C

    on TC.object_id=C.object_id

    where C.[name] = 'CreatedByID'

  • August 25, 2008 at 10:07 pm

    #862893

    This is exactly why adhoc updates to system tables are not allowed. If your UPDATE had been allowed to execute, you would have created a serious problem with the structure of the table. The information in sys.columns is used to determine how the data in the table is physically stored. Simply updating the column_id will confuse sql server and it is unlikely that you will be able to do anything to correct the problem.

    If you need to re-order the columns, the only way to do this is to re-create the table.

  • August 26, 2008 at 12:07 am

    #862912

    Colmn order doesn't really have a meaning in SQL. It just determines how the columns appear when the create table is scripted and how they are returned if SELECT * is used. If you want your columns returned in a specific order, write your select statements with that column last.

    You can drop the column and recreate it and then it will 'appear' at the end of the table when you're looking at the table's design

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 4, 2008 at 9:34 pm

    #867542

    Thankyou for responding to my questions.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply