Drop a column from table problem (SQL Server 2008)

Question

Unable to delete [U_Family] column from table below :

Table CREATE script:

CREATE TABLE [dbo].[Users](
    [U_Id] [int] IDENTITY(101,1) NOT NULL,
    [U_Name] [nvarchar](50) NULL,
    [U_Family] [nvarchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [U_Id] ASC
),
 CONSTRAINT [IX_UserIdUnique] UNIQUE NONCLUSTERED 
(
    [U_UserId] ASC
)
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Users(UserId)] ON [dbo].[Users] 
(
    [U_Id] ASC
)
INCLUDE ( [U_Name],
[U_Family]) ) ON [PRIMARY]
GO

error message :

'Users' table - Unable to modify table. The index 'IX_Users(UserId)' is dependent on column 'U_Family'. ALTER TABLE DROP COLUMN U_Family failed because one or more objects access this column.

I know that the problem is because of this index :

CREATE UNIQUE NONCLUSTERED INDEX [IX_Users(UserId)] ON [dbo].[Users] 
(
    [U_Id] ASC
)
INCLUDE ( [U_Name],
[U_Family])) ON [PRIMARY]
GO

but I never found a syntax to edit this index and remove [U_Family] from included columns.

I cannot delete this index because it is used by a foreign key constraint that I should not remove it.

any solution ????

thanks in advance.

Answer 1

In SQL Server 2008, you should be able to "re-create" your index and drop the existing one in a single command - try this:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Users(UserId)] 
  ON [dbo].[Users]([U_Id] ASC) 
  WITH DROP_EXISTING

The WITH DROP_EXISTING should drop the "old" index with the included columns. Once that command has been run, you should be able to drop your column from the table.