Sunday, July 16, 2006

USP_DropTableConstraints (SQL 2000 Version)

Drops Constraints for given table name (SQL 2000 Version) -
CREATE PROCEDURE USP_DropTableConstraints @tablename VARCHAR(128)    
AS

 SET NOCOUNT ON

 DECLARE  @constraintname VARCHAR(128),
          @sqlcmd         VARCHAR(1024)
                    
 DECLARE CONSTRAINTSCURSOR CURSOR  FOR
 SELECT NAME
 FROM   SYSOBJECTS
 WHERE  XTYPE IN ('C','F','PK','UQ',
                  'D')
        AND (STATUS & 64) = 0
        AND PARENT_OBJ = OBJECT_ID(@tablename)
                        
 -- nb : xtype refers to CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints.

 OPEN CONSTRAINTSCURSOR

 FETCH NEXT FROM CONSTRAINTSCURSOR
 INTO @constraintname

 WHILE (@@FETCH_STATUS = 0)
   BEGIN
     SELECT @sqlcmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraintname
     EXEC( @sqlcmd)
     FETCH NEXT FROM CONSTRAINTSCURSOR
     INTO @constraintname
   END
  
 CLOSE CONSTRAINTSCURSOR

 DEALLOCATE CONSTRAINTSCURSOR

 RETURN 0
       
GO

No comments: