복붙노트

[SQL] 2008 SQL에 외래 키 관계에서 삭제 정책을 생성?

SQL

2008 SQL에 외래 키 관계에서 삭제 정책을 생성?

이 테이블 FK 관계에 따라 삭제 문을 생성하는 스크립트 / 도구를 통해 가능하다.

즉, 내가 테이블이 있습니다 DelMe (ID)를 내가 먼저 삭제해야한다는 자사의 ID에 FK 참조 30 표가, 내가 그이 FK 관계에 따라 30 삭제 문을 생성합니다 실행할 수있는 몇 가지 도구 / 스크립트가 나를 위해?

(BTW 캐스케이드 관계에 삭제에 대해 내가 알고있는 나는이 기존 DB에서 사용할 수 없습니다)

나는 2008 Microsoft SQL Server를 사용하고 있습니다

해결법

  1. ==============================

    1.DELETE 문은 매개 변수를 사용하여 SP에 사용하기 위해 생성 및 DELETE 트리거 ON으로 : (이 변형은 단지 하나의 열 FKS 지원)

    DELETE 문은 매개 변수를 사용하여 SP에 사용하기 위해 생성 및 DELETE 트리거 ON으로 : (이 변형은 단지 하나의 열 FKS 지원)

    SELECT 'DELETE '+detail.name+' WHERE '+dcolumn.name+' = @'+mcolumn.name AS stmt, 
    'DELETE ' + detail.name + ' FROM ' + detail.name + ' INNER JOIN deleted ON ' + 
    detail.name + '.' + dcolumn.name + ' = deleted.' + mcolumn.name AS trg
FROM sys.columns AS mcolumn 
INNER JOIN sys.foreign_key_columns ON mcolumn.object_id = 
            sys.foreign_key_columns.referenced_object_id 
    AND  mcolumn.column_id = sys.foreign_key_columns.referenced_column_id 
INNER JOIN sys.tables AS master ON mcolumn.object_id = master.object_id 
INNER JOIN sys.columns AS dcolumn 
    ON sys.foreign_key_columns.parent_object_id = dcolumn.object_id 
    AND sys.foreign_key_columns.parent_column_id = dcolumn.column_id 
INNER JOIN sys.tables AS detail ON dcolumn.object_id = detail.object_id
WHERE (master.name = N'MyTableName')
  2. ==============================

    2.여기에, 아아 심 압둘라에 의해 삭제 계단식위한 스크립트입니다 MS SQL Server 2008에서 나를 위해 작동합니다 :

    여기에, 아아 심 압둘라에 의해 삭제 계단식위한 스크립트입니다 MS SQL Server 2008에서 나를 위해 작동합니다 :

    IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;

GO
CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
    DECLARE @schema_id AS BIGINT;
    SELECT @schema_id = schema_id
    FROM   sys.tables
    WHERE  object_id = @ObjectId;
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END

GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;

GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
    DECLARE @OnClauseTemplate AS VARCHAR (1000);
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';
    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM   dbo.sysforeignkeys AS fk
    WHERE  fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END

GO
--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;

GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='N', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys
SET NOCOUNT ON;
/* Set up debug */
DECLARE @DebugMsg AS VARCHAR (4000), 
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
    BEGIN -- assume owner is dbo and calculate id
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END
IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
    SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
    SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
    EXECUTE (@strSQL);
ELSE
    PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
                        fkeyid AS cTableId
        FROM   dbo.sysforeignkeys AS fk
        WHERE  fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
               AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT, 
@cTableId AS INT, 
@cColId AS INT, 
@pTableId AS INT, 
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;

    사용 예 (1)

    이 예에서 정규화 된 열 이름의 사용을합니다. 그것은 미묘하지만 생성 된 SQL이 제대로 실행하기 위해 당신은 테이블 이름을 지정해야합니다.

    EXEC uspCascadeDelete
@ParentTableId = 'Production.Location',
@WhereClause = 'Location.LocationID = 2'

    사용 예 2

    EXEC uspCascadeDelete
@ParentTableId = 'dbo.brand',
@WhereClause = 'brand.brand_name <> ''Apple'''

    사용 예 3

    exec uspCascadeDelete
@ParentTableId = 'dbo.product_type',
@WhereClause = 'product_type.product_type_id NOT IN 
(SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)'
  3. ==============================

    3.나는 확신 나는 동적 SQL을 생성하려면이 자동으로 사용 INFORMATION_SCHEMA를 수행 스택 오버플로 여기에 코드를 기록하고있어,하지만 난 그것을 찾을 수 없습니다. 나는 그것을 다시 생성 할 수 있는지 한번 보죠.

    나는 확신 나는 동적 SQL을 생성하려면이 자동으로 사용 INFORMATION_SCHEMA를 수행 스택 오버플로 여기에 코드를 기록하고있어,하지만 난 그것을 찾을 수 없습니다. 나는 그것을 다시 생성 할 수 있는지 한번 보죠.

    내가 자동으로 스타 스키마에 대한 flattend 뷰를 기반으로 내가 가진 몇 가지 코드를 수정 있도록, 내 원래의 코드를 찾을 수 없습니다, 비트에서이를 확인해야합니다.

    DECLARE @COLUMN_NAME AS sysname
DECLARE @TABLE_NAME AS sysname
DECLARE @IDValue AS int

SET @COLUMN_NAME = '<Your COLUMN_NAME here>'
SET @TABLE_NAME = '<Your TABLE_NAME here>'
SET @IDValue = 123456789

DECLARE @sql AS varchar(max) ;
WITH    RELATED_COLUMNS
          AS (
              SELECT    QUOTENAME(c.TABLE_SCHEMA) + '.'
                        + QUOTENAME(c.TABLE_NAME) AS [OBJECT_NAME]
                       ,c.COLUMN_NAME
              FROM      INFORMATION_SCHEMA.COLUMNS AS c WITH (NOLOCK)
              INNER JOIN INFORMATION_SCHEMA.TABLES AS t WITH (NOLOCK)
                        ON c.TABLE_CATALOG = t.TABLE_CATALOG
                           AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                           AND c.TABLE_NAME = t.TABLE_NAME
                           AND t.TABLE_TYPE = 'BASE TABLE'
              INNER JOIN (
                          SELECT    rc.CONSTRAINT_CATALOG
                                   ,rc.CONSTRAINT_SCHEMA
                                   ,lkc.TABLE_NAME
                                   ,lkc.COLUMN_NAME
                          FROM      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
                                    WITH (NOLOCK)
                          INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE lkc
                                    WITH (NOLOCK)
                                    ON lkc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
                                       AND lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
                                       AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                          INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    WITH (NOLOCK)
                                    ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
                                       AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                          INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE rkc
                                    WITH (NOLOCK)
                                    ON rkc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
                                       AND rkc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND rkc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                          WHERE     rkc.COLUMN_NAME = @COLUMN_NAME
                                    AND rkc.TABLE_NAME = @TABLE_NAME
                         ) AS j
                        ON j.CONSTRAINT_CATALOG = c.TABLE_CATALOG
                           AND j.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA
                           AND j.TABLE_NAME = c.TABLE_NAME
                           AND j.COLUMN_NAME = c.COLUMN_NAME
             )
    SELECT  @sql = COALESCE(@sql, '') + 'DELETE FROM ' + [OBJECT_NAME]
            + ' WHERE ' + [COLUMN_NAME] + ' = ' + CONVERT(varchar, @IDValue)
            + CHAR(13) + CHAR(10)
    FROM    RELATED_COLUMNS

PRINT @sql
  4. ==============================

    4.또 다른 기술은 SQL을 생성하는 코드 생성기를 사용하는 것입니다. 나는 꽤 확인 MyGeneration (연결)은 이렇게 템플릿을 기존했다 있어요. 이 도구를 사용하여 오른쪽 템플릿 당신은 고통없이 관련 물건을 삭제하는 SQL 스크립트를 생성 할 수 있습니다.

    또 다른 기술은 SQL을 생성하는 코드 생성기를 사용하는 것입니다. 나는 꽤 확인 MyGeneration (연결)은 이렇게 템플릿을 기존했다 있어요. 이 도구를 사용하여 오른쪽 템플릿 당신은 고통없이 관련 물건을 삭제하는 SQL 스크립트를 생성 할 수 있습니다.

  5. ==============================

    5.불행하게도, 나는 계단식 당신이 요구하고있는 도구라고 생각합니다. 나는 그것을 사용할 수있는,하지만이-에 내장 된 DB의 일부 꽤 많은 대안의 필요성을 죽인 것처럼이 존재한다는 사실하지 알고 있습니다.

    불행하게도, 나는 계단식 당신이 요구하고있는 도구라고 생각합니다. 나는 그것을 사용할 수있는,하지만이-에 내장 된 DB의 일부 꽤 많은 대안의 필요성을 죽인 것처럼이 존재한다는 사실하지 알고 있습니다.

    6. from https://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 데이터베이스를 사용하여 최대 절전 모드에서 다음 시퀀스 값을 가져  (0) 2020.04.19
[SQL] SQL Server 2005의 시간 차이를 찾기 위해 두 날짜를 비교하는 방법, 날짜 조작  (0) 2020.04.19
[SQL] 형식화 된 목록에서 IDataReader에 가져 오기  (0) 2020.04.19
[SQL] SQL Server에서 "SET ANSI_NULLS ON"은 무엇을 의미합니까?  (0) 2020.04.19
[SQL] 나는 VARCHAR 또는 INT의 기본 키가있는 테이블을 설계해야 하는가?  (0) 2020.04.19
,

티스토리툴바