How can I modify an identity field with my vb code

[wengyan]

I want modify a field from int(identity) to int(not identity),how can I do it in vb code?
Alter table command can't do it.whether SQL Server provide some api functions to do?

 

[StevenK]

What would be the impact of creating a new table with the appropriate structure, feeding the data from original table to new table and then removing the original table, renaming the new one to then have the appropriate name ?
Not sure if this would be convenient but it seems like one method (possibly one of your later resorts depending on other replies you might get).
Steve

 

[PCJock]

Maybe you could add a new column of type int. Run a query that sets the new column data to equal the identity column. Then delete the identity column.

 

[bperry]

Both of these suggestions are about the only ways to go.

One small advantage of PCJock's suggestion (a new column) is that the table permissions aren't affected. When going with the 'new table' approach, permissions would have to be re-applied to the new table.

Also, with PCJock's suggestion, if the identity column is the PK in that table, then you'll probably want to make some other column the PK (perhaps the new non-identity column?)

If there are other tables with FK constraints to your table, then you won't be able to delete the table unless you first remove the constraints.