T-SQL: Alter column to no longer be identity and remove constraints

Question

I'm trying to alter the column ID in the table [file].Item, so that it's no longer the identity of the table. I also want ot remove all foreign key constraints pointing at the column.

My problem is however that I want to do this with a script, which will remove all references to the column, and then drop it, so that I can remake it.

What I have so far is this:

exec sp_MSforeachtable 'alter table ? nocheck constraint all'

alter table [file].Item
drop column ID

alter table [file].Item
add ID integer not null

exec sp_MSforeachtable 'alter table ? with check check constraint all'

The error I get is this:

ALTER TABLE DROP COLUMN ID failed because one or more objects access this column.

EDIT

After looking into solution sugested to me, I've tried the following code:

CREATE TABLE test(
     [ID] [int] NOT NULL,
     [Parent_ID] [int] NULL,
     [Company_ID] [int] NOT NULL,
     [ItemType_ID] [int] NOT NULL,
     [ItemCode] [varchar](20) NOT NULL,
     [ItemNumber] [varchar](20) NOT NULL,
     [Type] [char](1) NOT NULL,
     [ItemDetails] [nvarchar](50) NULL,
     [Condition] [varchar](10) NULL,
     [DateTimeCreated] [datetime] NOT NULL,
     [DateTimeModified] [datetime] NOT NULL,
     [CreatorUser_ID] [int] NOT NULL,
     [LastModifierUser_ID] [int] NOT NULL,
     [Status] [tinyint] NOT NULL,
     [IsUploadToGlobal] [bit] NOT NULL,
     [Quantity] [numeric](18, 6) NOT NULL,
     [TotalQuantity] [numeric](18, 6) NOT NULL,
     [OnHand] [numeric](18, 6) NOT NULL,
     [Sold] [numeric](18, 6) NOT NULL,
     [OnOrder] [numeric](18, 6) NOT NULL,
     [MinQuantity] [numeric](18, 6) NULL,
     [MaxQuantity] [numeric](18, 6) NULL,
     [Name] [nvarchar](200) NULL,
     [LastInvoice_ID] [int] NULL,
     [OnPurchaseOrder] [numeric](18, 6) NOT NULL,
     [rv] [timestamp] NOT NULL,
     [ExternalCompanyID] [int] NULL,
     [ExternalCompanyName] [nvarchar](50) NULL,
     [ExternalItemID] [int] NULL,
     [LastOrder_ID] [int] NULL,
     [PreviousStatus] [tinyint] NULL,
     [DateTimeStatusChanged] [datetime] NULL,
     [Trashed] [numeric](18, 6) NOT NULL)


     ALTER TABLE test ADD  CONSTRAINT [DF_Item_DateTimeCreated]  DEFAULT (getutcdate()) FOR [DateTimeCreated]


     ALTER TABLE test ADD  CONSTRAINT [DF_Item_DateTimeModified]  DEFAULT (getutcdate()) FOR [DateTimeModified]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_Status]  DEFAULT ((0)) FOR [Status]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_IsUploadToGlobal]  DEFAULT ((1)) FOR [IsUploadToGlobal]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_Quantity]  DEFAULT ((0)) FOR [Quantity]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_TotalQuantity]  DEFAULT ((0)) FOR [TotalQuantity]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_OnHand]  DEFAULT ((0)) FOR [OnHand]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_Sold]  DEFAULT ((0)) FOR [Sold]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_OnOrder]  DEFAULT ((0)) FOR [OnOrder]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_OnPurchaseOrder]  DEFAULT ((0)) FOR [OnPurchaseOrder]


    ALTER TABLE test ADD  CONSTRAINT [DF_Item_Trashed]  DEFAULT ((0)) FOR [Trashed]

    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [FK_Item_ItemType] FOREIGN KEY([ItemType_ID])
    REFERENCES [ref].[ItemType] ([ID])


    ALTER TABLE test CHECK CONSTRAINT [FK_Item_ItemType]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [FK_Item_User] FOREIGN KEY([CreatorUser_ID])
    REFERENCES [system].[User] ([ID])


    ALTER TABLE test CHECK CONSTRAINT [FK_Item_User]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [FK_Item_User1] FOREIGN KEY([LastModifierUser_ID])
    REFERENCES [system].[User] ([ID])


    ALTER TABLE test CHECK CONSTRAINT [FK_Item_User1]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_ActivePartNotSold] CHECK  (([Type]<>'P' OR [Type]='P' AND ([Status]>(1) OR [Status]<=(1) AND [Sold]=(0))))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_ActivePartNotSold]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_Available] CHECK  ((([OnHand]-[OnOrder])=[Quantity]))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_Available]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_OnOrder] CHECK  (([OnOrder]>=(0) AND [OnOrder]<=[TotalQuantity]))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_OnOrder]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_OnPurchaseOrder] CHECK  (([OnPurchaseOrder]>=(0)))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_OnPurchaseOrder]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_Quantity] CHECK  (([Quantity]>=(0) AND [Quantity]<=[TotalQuantity]))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_Quantity]


    ALTER TABLE test WITH CHECK ADD  CONSTRAINT [CK_Item_ReservedParts] CHECK  (([Type]<>'P' OR [Type]='P' AND ([Status]<>(100) OR [Status]=(100) AND [OnOrder]=[TotalQuantity])))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_ReservedParts]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_SoldParts] CHECK  (([Type]<>'P' OR [Type]='P' AND ([Status]<>(150) OR [Status]=(150) AND [Sold]=[TotalQuantity])))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_SoldParts]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_ToralQuantity] CHECK  (([TotalQuantity]=((([Quantity]+[Sold])+[OnOrder])+[Trashed])))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_ToralQuantity]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_Trashed] CHECK  (([Trashed]>=(0) AND [Trashed]<=[TotalQuantity]))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_Trashed]

    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_TrashedParts] CHECK  (([Type]<>'P' OR [Type]='P' AND ([Status]<>(220) OR [Status]=(220) AND [Trashed]=[TotalQuantity])))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_TrashedParts]


    ALTER TABLE test  WITH CHECK ADD  CONSTRAINT [CK_Item_Type] CHECK  (([Type]='C' AND [Parent_ID] IS NULL OR [Type]='P' OR [Type]='N'))


    ALTER TABLE test CHECK CONSTRAINT [CK_Item_Type]


     alter table [file].Item switch to test

     select * from test

     drop table [file].Item

     exec sp_rename 'test', '[file].Item'

     select * from [file].Item

The error I get now is:

ALTER TABLE SWITCH statement failed. The table 'file.Item' has clustered index 'PK_Item' while the table 'test' does not have clustered index.

Try running `exec sp_MSforeachtable 'alter table ? nocheck constraint all'` alone then run drop column — Pரதீப், Aug 25 '16 at 09:37
Also `alter table [file].Item add ID integer not null` this is not possible with data present in table — Pரதீப், Aug 25 '16 at 09:38
You may be able to do something quite similar to [this answer](http://stackoverflow.com/a/1730868/15498) (but in reverse) to take off the identity property without dropping the column. — Damien_The_Unbeliever, Aug 25 '16 at 09:47
@Damien_The_Unbeliever: I've tried what you suggested, but without further success — Michael Tot Korsgaard, Aug 25 '16 at 12:22

score 0 · Answer 1 · answered Aug 25 '16 at 10:25

Disable the constraints on a table :

  ALTER TABLE YourTable NOCHECK CONSTRAINT ALL

Re-enable the constraints on a table :

 ALTER TABLE YourTable WITH CHECK CHECK CONSTRAINT ALL

Disable constraints for all tables:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Re-enable constraints for all tables:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

T-SQL: Alter column to no longer be identity and remove constraints

1 Answers1