Identity Columns

I had a table with identity column.
started dumping of data into it.
After that i want to drop the identity constraint on that column using t-sql script.

is it possible? if so how can i do it.

Nope, you cannot remove the identity property from a column. What you can do is to transfer the values to a different column and drop the column:

create table testIdentity
(
identityColumn int identity
)
go
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
go
alter table testIdentity
add nonIdentityColumn int null
go
update testIdentity
set nonIdentityColumn = identityColumn
go
alter table testIdentity
drop column identityColumn
go
select *
from testIdentity

|||Thanks.

But i would like to know why it is not possible through t-sql while we are able to do it from EM|||

Because EM does something along the lines of what I did. EM actually is pretty inefficient in many cases in how it performs operations (like adding a new column it does a drop column then and adds a new column where an ALTER TABLE would suffice). But it is usually understandable as it takes the easiest, most straightforward path for automation, rather than a method that looks better but cannot be automated quite as easy.

One thing to try is to trace what EM does using profiler. That is a great place to see the queries it does. In Management Studio, I changed the identity property, and these are the action queries that were sent:

CREATE TABLE dbo.Tmp_testIdentity
(
IdentityColumn int NOT NULL
) ON [PRIMARY]

go
IF EXISTS(SELECT * FROM dbo.testIdentity)
EXEC('INSERT INTO dbo.Tmp_testIdentity (IdentityColumn)
SELECT IdentityColumn FROM dbo.testIdentity WITH (HOLDLOCK TABLOCKX)')

go
DROP TABLE dbo.testIdentity

go
EXECUTE sp_rename N'dbo.Tmp_testIdentity', N'testIdentity', 'OBJECT'

go

The method I gave you is far less distructive, but either will work.