Professional Web Applications Themes

Remove Identity Property - Microsoft SQL / MS SQL Server

Sidney, You cannot add/alter the identity property for a column in sqlserver via t-sql. You would need to create/add a new column with such property or you could use enterprise mgr to set the identity property. What really happens behind the scene is that EM would create a temp table, bcp data, drop the original tb, and rename. -- -oj RAC v2.2 & QALite! http://www.rac4sql.net "sydney" <ucsf.edu> wrote in message news:05fe01c350a8$564d4c20$gbl... ...

  1. #1

    Default Re: Remove Identity Property

    Sidney,

    You cannot add/alter the identity property for a column in sqlserver via
    t-sql. You would need to create/add a new column with such property or you
    could use enterprise mgr to set the identity property. What really happens
    behind the scene is that EM would create a temp table, bcp data, drop the
    original tb, and rename.


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "sydney" <ucsf.edu> wrote in message
    news:05fe01c350a8$564d4c20$gbl... 


    oj Guest

  2. #2

    Default Remove Identity Property

    Not doented way to add identity property to existing
    column, MSSQL2k:

    exec sp_configure 'allow updates',1
    reconfigure with override
    go

    Begin tran
    update sysobjects set status=status+32 where (status & 32
    = 0)
    and name = 'Test'

    update syscolumns set
    autoval=0x01000000010000000100000003, colstat = 1 where
    id=OBJECT_ID('Test') and name='Test_ID'

    dbcc CHECKIDENT('Test')
    commit tran
    go

    exec sp_configure 'allow updates',0
    reconfigure with override
    go

    Removing identity property quite similar, but I have no
    such script now. I heard some using the script on
    production databases, and it work without errors (but I
    prefer to use usual ways).
     
    do. . . 
    andsm Guest

  3. #3

    Default Re: Remove Identity Property

    Yes, clearly there is much that goes on behind the scenes, including
    dropping and recreating all constraints and relationships with the table
    in question, but if it can be done through the GUI (albeit with a
    confirmation dialog about saving changes to all the related tables), I
    suspect the existence of an undoented procedure to accomplish it.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    sydney Guest

Similar Threads

  1. How to remove a property?
    By Showjumper in forum ASP.NET Building Controls
    Replies: 1
    Last Post: July 24th, 03:54 AM
  2. Remove an inherited property?
    By Curt_C [MVP] in forum ASP.NET Building Controls
    Replies: 2
    Last Post: September 6th, 12:57 PM
  3. SET IDENTITY PROPERTY OF COLUMN?
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 02:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139