儲存過程獲取表被引用的資訊

iSQlServer發表於2009-07-13
儲存過程
可能題目取得不是很好，但我想看了文章就知道什麼意思。
當你想刪除一個表時，可能被其他表作為外來鍵被引用，此時你想刪除此表是刪除不掉的。你必須把那引用此表為外來鍵的表刪掉，才能把你要刪的表的刪掉。可能文字不好表達。我就舉個例子
當b，c表引用a表作為外來鍵，你想刪a表的某些資料，就一定要刪除相關的b，c中引用a表的資料。可是b，c表可能又被其他表引用，這樣肯定會導致一個遞迴。
如果按正常方法去刪a表，你可能寫刪除語句來刪除a表，但執行時會報錯說那個表引用了此表，此時你根據報錯資訊會找到引用的表，又刪除，又報錯，又根據資訊查詢，此時我感覺很麻煩。
我就自己寫了個儲存過程。

按理來說要獲取引用表的資訊應該用遞迴來解決最好，但我試過，感覺在資料庫中運用得沒有c#好，才改變策略用過程來寫。

程式碼如下

儲存過程獲取表被引用的資訊
CREATE PROCEDURE dbo.usp_GetFeferenceTableInfo
@TableName nvarchar (50)
AS

SET NOCOUNT ON 
  CREATE   TABLE   #ForeignKeyTABLEInfo
 (   
    PKTABLE_QUALIFIER   sysname   collate   database_default   NULL,   
    PKTABLE_OWNER   sysname   collate   database_default   NULL,   
    PKTABLE_NAME   sysname   collate   database_default   NOT   NULL,   
    PKCOLUMN_NAME   sysname   collate   database_default   NOT   NULL,   
    FKTABLE_QUALIFIER   sysname   collate   database_default   NULL,   
    FKTABLE_OWNER   sysname   collate   database_default   NULL,   
    FKTABLE_NAME   sysname   collate   database_default   NOT   NULL,   
    FKCOLUMN_NAME   sysname   collate   database_default   NOT   NULL,   
    KEY_SEQ   smallint   NOT   NULL,   
    UPDATE_RULE   smallint   NULL,   
    DELETE_RULE   smallint   NULL,   
    FK_NAME   sysname   collate   database_default   NULL,   
    PK_NAME   sysname   collate   database_default   NULL,   
    DEFERRABILITY   smallint   null
 )   
  DECLARE @Level int 
  DECLARE @ParentGetSubForNull int
  DECLARE @SQL nvarchar (max)
  SET @Level=0
  SET @ParentGetSubForNull=1000
  

 WHILE @ParentGetSubForNull<>0
    BEGIN
        PRINT @Level
        IF @Level<>0 
            BEGIN 
                SET @SQL=''
                SELECT  @SQL=@SQL+'INSERT   INTO   #ForeignKeyTABLEInfo EXEC   sp_fkeys  '+FKTABLE_NAME+''+CHAR(13)+CHAR(10) FROM #ForeignKeyTABLEInfo WHERE  UPDATE_RULE=@Level
                EXEC(@SQL)
                PRINT @SQL
            END 
        ELSE 
            INSERT   INTO   #ForeignKeyTABLEInfo     EXEC   sp_fkeys  @TableName 
        
        SELECT @ParentGetSubForNull=COUNT(*) FROM #ForeignKeyTABLEInfo WHERE KEY_SEQ=1

        IF EXISTS (SELECT 1 FROM #ForeignKeyTABLEInfo WHERE KEY_SEQ=1)
        BEGIN
            UPDATE #ForeignKeyTABLEInfo SET UPDATE_RULE=@Level+1 WHERE KEY_SEQ=1
            UPDATE #ForeignKeyTABLEInfo SET KEY_SEQ=2 
        END
            
        SET @Level=@Level+1
        
   END
 
   SELECT PKTABLE_NAME,PKCOLUMN_NAME,[Level]=UPDATE_RULE,FK_NAME,PK_NAME FROM #ForeignKeyTABLEInfo

 SET NOCOUNT OFF

 返回的結果有幾個欄位，其中Level表示表被引用的深度，就是b，c引用a，d引用b，f引用c，則b，c表的深度是1，d，f表的深度是2.此時刪a表直接可以從d，f開始刪，從深度高到低來刪表。還一個欄位是PK_NAME表示那些表引用了要刪的表。其他欄位應該很清楚。

不知道誰能用遞迴實現此功能 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-608983/，如需轉載，請註明出處，否則將追究法律責任。

相關文章