If you need to keep the data in that column then create a new column on the table which is of the same type (but a different name), copy the data from the column you want to get rid of to the new one, drop the old column and rename the new. Complete example:

CREATE TABLE test(col1 INT IDENTITY (1,1) NOT NULL, col2 VARCHAR(10) NULL);

ALTER TABLE test ADD col3 INT NULL;

UPDATE test SET col3 = col1;

ALTER TABLE test DROP COLUMN col1;

EXEC sp_rename 'dbo.test.col3', 'col1', 'COLUMN';

The easiest way would be:

1. Open SQL Server Management Studio .
2. Locate Server > DataBase > Table.
3. Right Click on the Table > Select Design.
4. In the design window, Highlight the column you want to modify.
5. In the Column Properties Window browse to Identity Specification > Is Identity And set to No .
6. Go to the toolbar menu > Table Designer > Select Generate Change Script...
7. Walla, you got the requested script.

I Like using this method for getting scripts, since it allows me to generate scripts I'm not sure how to compose from scratch and thus learning and improving my skills...

If it's a primary key column, then you have to drop the PK first. If there's any tables referencing it, then you'll have to drop these FKs to be able to drop the PK. After that, add another column of the same type, update it with values from identity column, drop the identity column, rename the new column to whatever the name of identity column was (with sp_rename procedure), recreate the PK, recreate the FKs, check if everything went right.

I'd be very careful doing it on a production database. Ensure that noone can access the data while you're doing this.

I searched a lot to find a simple solution to remove the auto increment because i should do a lot of work if i drop the column and the primary key which was a foreign key on another table where I should remove the data that are using my foreign ... finally I ended up with a very simple solution that made my life easy:

SET IDENTITY_INSERT <table_name> ON ;

SET IDENTITY_INSERT [TABLE] OFF .. this allows to remove the auto increment to off state.., so that we have to enter the value in thatcolumn

You can remove 'auto_increment' by using following query...

ALTER TABLE <table_name> MODIFY <auto_incremented_column_name> <column_data_type>;

For exmample,

table_name = STUDENT,

auto_incremented_column_name = ID,

column_data_type = INT

ALTER TABLE STUDENT MODIFY ID INT;

To add 'auto_increment'

ALTER TABLE STUDENT MODIFY ID INT AUTO_INCREMENT;