Drop FK For A Specific Table

,

A Simple code.. allow you to drop all FK (foreign keys) for a table, you can use @option for generate only the script or execute ( 1 execute, 0 generate).

ALTER PROC HND_DBADROPFK
@table sysname, @option bit
--Antunez 2008.04.24 Initial Code
AS
BEGIN
declare @sql4 as nvarchar(400)
declare @fk as varchar(80)
PRINT '--User: '+user + ' '+system_user +' Script Date: ' + cast (getdate() as varchar(40))
DECLARE FK CURSOR FOR 
select constraint_name from information_schema.table_constraints 
where table_name = @table and constraint_type = 'FOREIGN KEY' 
OPEN FK
FETCH NEXT FROM FK into @fk
WHILE @@FETCH_STATUS = 0 
BEGIN
set @sql4 = 'ALTER TABLE ' + RTRIM(@table) + ' DROP CONSTRAINT ' + @fk
PRINT @sql4
IF @option = 1
execute sp_executesql @sql4
FETCH NEXT FROM FK into @fk
END
CLOSE FK
DEALLOCATE FK
END

--HND_DBADROPFK 'Customer' ,0

Rate

4 (2)

Share

Share

Rate

4 (2)