I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread: http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.
table is too large but I have taken a small example
there large data in I want not to drop
if there is any query are way to remove IDENTITY(1,1) without effecting
No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...
As a matter of fact, do you know how save a script in Enterprise Mangler?
go create a test table with an identity column
Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do
Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself
Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old
Again, how many rows are we talking about
Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right....and then do
I want to remove IDENTITY(1,1) from id without dropping table
Dropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.
The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.
There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.