sp_deletefield.sql

来自「WAPmo手机网站管理平台是一款创建与管理维护WAP网站的的软件产品」· SQL 代码 · 共 32 行

CREATE PROC SP_DELETEFIELD
    @TBNAME SYSNAME,
    @FDNAME SYSNAME,
    @DELFIELD BIT=1
    AS
    DECLARE HCFOREACH CURSOR GLOBAL FOR
    SELECT SQL='ALTER TABLE ['+B.NAME+'] DROP CONSTRAINT ['+D.NAME+']'
    FROM SYSCOLUMNS A
        JOIN SYSOBJECTS B ON A.ID=B.ID AND A.NAME=@FDNAME AND B.NAME=@TBNAME 
        JOIN SYSCOMMENTS C ON A.CDEFAULT=C.ID
        JOIN SYSOBJECTS D ON C.ID=D.ID
    UNION
    SELECT S='ALTER TABLE ['+C.NAME+'] DROP CONSTRAINT ['+B.NAME+']'
    FROM SYSFOREIGNKEYS A
        JOIN SYSOBJECTS B ON B.ID=A.CONSTID
        JOIN SYSOBJECTS C ON C.ID=A.FKEYID
        JOIN SYSCOLUMNS D ON D.ID=C.ID AND A.FKEY=D.COLID AND D.NAME=@FDNAME
        JOIN SYSOBJECTS E ON E.ID=A.RKEYID AND E.NAME=@TBNAME
        JOIN SYSCOLUMNS F ON F.ID=E.ID AND A.RKEY=F.COLID
    UNION
    SELECT CASE WHEN E.XTYPE IN('PK','UQ') THEN 'ALTER TABLE ['+C.NAME+'] DROP CONSTRAINT ['+E.NAME+']'
            ELSE 'DROP INDEX ['+C.NAME+'].['+A.NAME+']' END
    FROM SYSINDEXES A
        JOIN SYSINDEXKEYS B ON A.ID=B.ID AND A.INDID=B.INDID
        JOIN SYSOBJECTS C ON B.ID=C.ID AND C.XTYPE='U' AND C.NAME=@TBNAME
        JOIN SYSCOLUMNS D ON B.ID=D.ID AND B.COLID=D.COLID AND D.NAME=@FDNAME
        LEFT JOIN SYSOBJECTS E ON E.ID=OBJECT_ID(A.NAME)
    WHERE A.INDID NOT IN(0,255)
    EXEC SP_MSFOREACH_WORKER '?'
    IF @DELFIELD=1
        EXEC('ALTER TABLE ['+@TBNAME+'] DROP COLUMN ['+@FDNAME+']')