在动态创建数据库的表的字段时,如果该字段的“是否为空”的属性为非空,且该表与其他表有联系,那么动态删除该列可能失败。报错原因因为有一个或多个对象访问此列。解决方法是先删除在该列上的约束,然后在删除该列!
存储过程如下:
CREATE PROCEDURE PR_DELTABLEROW
@TABLENAME VARCHAR(100),@DATAFIELDNAME VARCHAR(100)
AS
BEGIN TRAN
DECLARE @SQLDEL VARCHAR(500),@DF_NAME VARCHAR(500)
SET @SQLDEL=''
/*判断该列是否存在*/
IF exists(select 1 from syscolumns where id=object_id(@TABLENAME) and name= @DATAFIELDNAME)
BEGIN
/*查找该列的约束*/
SELECT @DF_NAME =NAME FROM SYSOBJECTS WHERE XTYPE='D' AND PARENT_OBJ=OBJECT_ID(@TABLENAME) AND EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) AND INFO=COLID AND NAME = @DATAFIELDNAME)
/*删除该列的约束*/
SET @SQLDEL ='ALTER TABLE ' + @TABLENAME + ' DROP CONSTRAINT '+ @DF_NAME
EXEC (@SQLDEL)
/*现在就可以该列了*/
SET @SQLDEL = 'ALTER TABLE ' + @TABLENAME + ' DROP COLUMN ' + @DATAFIELDNAME
EXEC (@SQLDEL)
END
if( @@ERROR = 0 )
COMMIT TRAN
else
ROLLBACK TRAN