Drop a column from table problem (SQL Server 2008)

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 pr开发者_开发问答oblem 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.

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.

You will have to drop the index IX_Users and create a new one without U_Family.

I used sys.indexes to delete the index and then drop the column:

DECLARE @sql VARCHAR(max)

SELECT @sql = 'DROP INDEX ' + idx.NAME + ' ON tblName'
FROM sys.indexes idx
INNER JOIN sys.tables tbl ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id
WHERE idx.type <> 0
    AND tbl.NAME = 'tblName'
    AND col.NAME = 'colName'

EXEC sp_executeSql @sql
GO

ALTER TABLE tblName
DROP COLUMN colName

I found this solution and worked for me:

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE 
PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT 
column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = 
OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
   EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND 
name='__ColumnName__')
   EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Just replace __TableName__ and __ColumnName__.