引用邹老大的关于相关表删除的代码，我学好多：

 

-- SQL Server 2000 对错误处理不好控制, 一般还是建议做判断

-- 通过系统表查询系统表，可以获取某个表关联的所有外键表

 

-- 示例存储过程

CREATE PROC dbo.p_Delete

    @tbname sysname,        -- 基础数据表名

    @PkFieldName sysname,   -- 基础数据表关键字段名

    @PkValue int            -- 要删除的基础数据表关键字值

AS

SET NOCOUNT ON

DECLARE @bz bit, @s nvarchar(4000)

DECLARE tb CURSOR LOCAL

FOR

----------------------------------------------

SELECT N'

SET @bz = CASE WHEN EXISTS(

        SELECT * FROM ' + QUOTENAME(@tbname)

        + N' A, ' + QUOTENAME(OBJECT_NAME(B.fkeyid))

        + N' B

        WHERE A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))

        + N' = B.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.fkey AND id = B.fkeyid))

        + N' AND A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))

        + N' = @id) THEN 1 ELSE 0 END'

FROM sysobjects A

    JOIN sysforeignkeys B

        ON A.id= B.constid

    JOIN sysobjects C

        ON A.parent_obj = C.id

WHERE A.xtype = 'f'

    AND C.xtype = 'U'

    AND OBJECT_NAME(B.rkeyid) = @tbname

OPEN tb

FETCH tb INTO @s

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC sp_executesql @s, N'@tbname sysname, @id int, @bz bit OUT', @tbname, @PkValue, @bz OUT

    IF @bz = 1

    BEGIN

        SET @s = N'UPDATE ' + QUOTENAME(@tbname)

            + N' SET bz = 1 WHERE ' + QUOTENAME(@PkFieldName)

            + N' = @id'

        EXEC sp_executesql @s, N'@id int', @PkValue

 

        RETURN

    END

 

    FETCH tb INTO @s

END

CLOSE tb

DEALLOCATE tb

 

SET @s = N'DELETE FROM ' + QUOTENAME(@tbname)

    + N' WHERE ' + QUOTENAME(@PkFieldName)

    + N' = @id'

EXEC sp_executesql @s, N'@id int', @PkValue

GO

 

 关于学习如下：

1，要在多表关联下删除数据，要判断sysobjects,sysreferences,把相关的表查出来

2，上面要用sp_executesql 'sqlspring',param(它为sqlspring我参数),  如id

3,关于sysobjects(xtype='f',id--数据对象标识)

4,关于sysforeignkeys（constid, 约束ID，用于多个约束引用，rkey外键约束中的引用的主键id,fkey 外键表的列id）

   rkeyid外键约束下的主表的ID，fkeyid外键约束下的从表ID，