October 24, 2011 at 2:34 pm
Hi Everyone,
I need a script to drop a column in a table that is FK in another table, and it’s an index too. This script must drop the constraints and indexes that depend on this column, and drop the column too.
I already tried to drop directly the column, but SQL Server gives me the following error:
Msg 5074, Level 16, State 1, Line 1
The object 'PK__Patologia__7A9C383C' is dependent on column 'IdPatologia'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK__Protocolo__IdPat__53D770D6' is dependent on column 'IdPatologia'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN IdPatologia failed because one or more objects access this column.
Thanks a lot.
October 24, 2011 at 3:30 pm
IIRC the correct drop sequence is FK, then index, defaults, checks, schemabound objects and then the column.
If other fks use that column as PK in the relation those will need tobe dropped as well.
October 25, 2011 at 12:39 am
you have to first drop the dependent object related to that, find the indexes, constraints on that column and the delete those first and then that column.
October 25, 2011 at 8:11 am
Hello guys,
I managed to get a query to drop the column. Follow the marked, first delete the FK contraints and indexes. Actually drop these two objects were sufficient. Tks for you tips. Below is the script to support colleagues:
begin tran
Declare @Table varchar(50)
Declare @ColumnFK varchar(50)
Declare @TableFK varchar(50)
Declare @Constraint_FK Varchar(250)
Declare @SqlDropFk varchar(MAX)
declare @IndexFK nvarchar(50)
Declare @ColumnIndexFK varchar (50)
DECLARE @NOMBRE AS sysname
declare @sqlExe varchar(50)
--Set's
Set @Table = 'Patologia'
Set @ColumnFK = 'IdPatologia'
Set @TableFK = 'Protocolo'
Set @Constraint_FK =
(SELECT
RC.Constraint_Name AS FK_Constraint--,
FROM
information_schema.referential_constraints RC JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
where CCU2.TABLE_NAME = @Table AND CCU2.COLUMN_NAME = @ColumnFK and CCU.TABLE_NAME = @TableFK
)
set @SqlDropFk = 'alter table '+ @TableFK + ' drop constraint ' + @Constraint_FK
exec(@SqlDropFk)
set @ColumnIndexFK = '%IdPatologia%'
select @NOMBRE ='Protocolo'
set @IndexFK =
(
SELECT
sys.indexes.name AS index_name
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN (
SELECT DISTINCT sys.index_columns.object_id, sys.index_columns.index_id
FROM
(
SELECT object_id, column_id
FROM sys.index_columns
WHERE key_ordinal=1 AND is_included_column=0
GROUP BY object_id, column_id
) AS index_columns_dupe
JOIN sys.index_columns ON index_columns_dupe.object_id=sys.index_columns.object_id AND index_columns_dupe.column_id=sys.index_columns.column_id AND sys.index_columns.key_ordinal=1
) AS dupe_index_objects ON sys.objects.object_id=dupe_index_objects.object_id
JOIN sys.indexes ON sys.objects.object_id=sys.indexes.object_id AND dupe_index_objects.index_id=sys.indexes.index_id
JOIN (
SELECT
object_id, index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
WHERE
sys.objects.name =@NOMBRE and index_columns_key like @ColumnIndexFK
)
--select @IndexFK
set @sqlExe = 'drop index ' + @IndexFK + ' ON ' + @NOMBRE
exec(@sqlExe)
Declare @SqlDropColumn varchar(150)
Set @SqlDropColumn = 'Alter Table '+ @TableFK + ' Drop Column '+ @ColumnFK
exec(@SqlDropColumn)
Commit tran
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply