hello
check out this
How do I remove the IDENTITY property from an existing column?
http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html# Manager drops the IDENTITY property from an existing column in much the same way that it adds the IDENTITY property, but you might want to consider alternatives to using Enterprise Manager if you never update the OrderID column. For example, when you want to insert your own order IDs rather than setting the IDENTITY column property to generate the order IDs automatically, you can turn on the IDENTITY_INSERT session option on the Orders2 table from the previous Q&A before you insert http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html#. Note that a session option has two main features. The option affects only the session in which you issued the statement (in this case SET IDENTITY_INSERT <table_name> ON). The option lets you insert your own values into the column but not update it.
To allow column updates, you have to drop the column's IDENTITY property. If you remove the property through Enterprise Manager, the code that Enterprise Manager generates creates a new table, http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html# all the data from the original table to the new table, then renames the new table with the original table's name. However, if you prefer a shorter procedure that doesn't involve copying all the original data to another table and dropping the original table, you can write your own shortcut script. The shortcut script that http://www.sqlmag.com/Files/23/22081/Listing_03.txt shows adds a single column, updates its data to the IDENTITY column values, then drops the IDENTITY column. . . .
reference
http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html
code here
LISTING 3: Code That Drops the IDENTITY Property from an Existing Column
--If other tables’ foreign keys point to the IDENTITY column, drop them. Here, the script drops
--the foreign key pointing to Orders2.OrderID from OrderDetails2.
ALTER TABLE OrderDetails2
DROP CONSTRAINT FK_OrderDetails2_Orders2
--If a primary key exists on the IDENTITY column, drop the primary key constraint the way this
--script drops the primary key from Orders2.
ALTER TABLE Orders2
DROP Constraint PK_Orders2
--Add another column with the same data type as the IDENTITY column to Orders 2 and allow
--NULLs.
ALTER TABLE Orders2
ADD new_OrderID int NULL
--Update the new column with the values of the IDENTITY column.
UPDATE Orders2
SET new_OrderID = OrderID
--If the new column doesn’t permit NULLs, alter the column to NOT NULL.
ALTER TABLE Orders2
ALTER COLUMN new_OrderID int NOT NULL
--Drop the IDENTITY column.
ALTER TABLE Orders2
DROP COLUMN OrderID
--Rename the new column to the dropped IDENTITY column’s name.
EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN'
--If a primary key exists on the new column, recreate the key. In this case you recreate the
--primary key on Orders2.
ALTER TABLE Orders2
ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID)
--On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column
--and point them to the new column. Here, you recreate the foreign key on OrderDetails2.
ALTER TABLE OrderDetails2 WITH NOCHECK
ADD CONSTRAINT FK_OrderDetails2_Orders2
FOREIGN KEY(OrderID)
REFERENCES Orders2(OrderID)
#articleQuickSubWrapper {
width: 590px;
background-color: #FFFFFF;
text-align: left;
}
#articleQuickSubWrapper img {
float: left;
margin-top: 10px;
}
#articleQuickSubWrapper #theForm {
width: 440px;
float: right;
margin-top: 25px;
}
#articleQuickSubWrapper #theForm * {
font-size: 11px;
}
#articleQuickSubWrapper #theForm label {
width: 90px;
display: -moz-inline-box;
display: inline-block;
text-align: right;
}
#articleQuickSubWrapper #theForm input,
#articleQuickSubWrapper #theForm select {
width: 115px;
}
#articleQuickSubWrapper #theForm input#theFormSubmit {
width: auto;
margin-top: 10px;
}
#articleQuickSubWrapper #theForm p {
margin-top: 10px;
font-size: 11px;
text-align: center;
}