Technical Article

Deletes all Constraints from Table

,

--Delete all restrictions from those tables included in the input parameters
Procedure HP_BORRA_CONSTRAINTS 
-- Include CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and  DEFAULT constraints. 

/*key/index deleting  */if exists (select * from sysobjects where id = object_id(N'[dbo].[HP_BORRA_CONSTRAINTS]') 
            and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HP_BORRA_CONSTRAINTS]
GO

create proc HP_BORRA_CONSTRAINTS

@tablenamesysname 
                   

as

-- name:   HP_BORRA_CONSTRAINTS


-- HP_BORRA_CONSTRAINTS BORRA TODAS LAS RESTRICCIONES DE LAS TABLAS ESPECIFICADAS, 
-- INCLUYE CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, Y  DEFAULT constraints.  


set nocount on

declare @constnamesysname,
@cmdvarchar(1024)

declare curs_constraints cursor for
select name
from sysobjects 
where xtype in ('C', 'F', 'PK', 'UQ', 'D')
and(status & 64) = 0
       /*  Date will be modified as needed */        and     refdate >= '10/08/2001'
        and     parent_obj = object_id(@tablename)

open curs_constraints

fetch next from curs_constraints into @constname
while (@@fetch_status = 0)
begin
select @cmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constname
exec(@cmd)
fetch next from curs_constraints into @constname
end

close curs_constraints
deallocate curs_constraints

return 0


GO

Rate

Share

Share

Rate