Bob Hovious (12/29/2008)
Matt, that was my first thought, but there's a problem if they just defined the identity property for the column without expressly creating a constraint.Try this:
Create table dbo.dummy (ID int identity(1,1) primary key, filler char(100))
select * from information_schema.constraint_column_usage
where table_name = 'dummy'
There's a constraint on the ID column for the primary key, but not one for identity. You can drop that constraint and ID is no longer the primary key, but it is still an identity column.
Ack - read that backwards.... He wants to KEEP the PK, just not the Identity property.
Agreed - no way to do that without what will end up being a "drop and rebuild table" process. Even doing it from SSMS will drop and recreate the whole table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?