Post reply

Create A Table With Non Contiguous Column IDs

  • July 22, 2004 at 4:36 am

    #88172

    I am recording details of tables and columns to enable me to recreate them on another Site/DB.

    Is there a way of generating a table via T-SQL that will ensure the column ids match.

    Eg A table with 5 columns will have column ids 1-5

    Delete column 3. The column ids will run 1-2 4-5.

    At the moment I am creating the table with a dummy field then dropping it afterwards which is messy.

    Alternately is there a way to force a table with non contiguous column ids to shuffle them up?

    Thanks

  • July 22, 2004 at 7:55 am

    #515677

    There might be, but how do your determine a 4 column table has columns 1,2,4,5 and not 1,2,3,4? If you can explain that logic, I'm sure someone can generate some T-SQL to solve this.

  • July 22, 2004 at 8:41 am

    #515698

    I use this TSQL:

    Declare @TableID Int

    Set @TableID=(Select ID From SysObjects Where xtype='u' and name='MyTable')

    Select sc.name, sc.id, sc.colid, datatype=st.name, sc.length, sc.prec, 

               sc.scale, IsID=Case When sc.autoval Is Null Then 0 Else 1 End, 

               allownulls=sc.isnullable, defaultvalue=sm.text, formula=' ', sc.collation 

    From SysColumns sc 

    Left Join SysTypes st on sc.xtype=st.xtype 

    Left Join SysComments sm on sc.cdefault=sm.id 

    Where sc.ID=@TableID AND st.name<>'sysname' Order By sc.colid Asc

    Please excuse the fudge for ID checking! This routine comes after a section that has already dealt with the table so the ID is passed to it.

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

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