[KB]How to find dependency in SQL server 2005

如果你是一個DBA，常常會有清理資料庫內不需要的物件的需求。

但是你又怕砍掉這些物件(Column、Table、View及Store Procedure等)會讓其他依賴這物件的物件變得沒有作用。你可以透過下列語法來建立一個Store Procedure來識別物件的Dependency。

USE master
GO
CREATE PROCEDURE sp_FindDependencies
(
        @ObjectName SYSNAME,
        @ObjectType VARCHAR(5) = NULL
)
AS
BEGIN
    DECLARE @ObjectID AS BIGINT 

        SELECT TOP(1) @ObjectID = object_id
        FROM sys.objects
        WHERE name = @ObjectName
        AND type = ISNULL(@ObjectType, type) 

    SET NOCOUNT ON ; 

      WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
        AS
        (
        SELECT DISTINCT
               sd.object_id,
               OBJECT_NAME(sd.object_id),
               ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
               ReferencedObjectID = sd.referenced_major_id
        FROM    
               sys.sql_dependencies sd
               JOIN sys.objects so ON sd.referenced_major_id = so.object_id
        WHERE   
               sd.referenced_major_id = @ObjectID
        UNION ALL
        SELECT
               sd.object_id,
               OBJECT_NAME(sd.object_id),
               OBJECT_NAME(referenced_major_id),
               object_id
        FROM    
               sys.sql_dependencies sd
            JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID       
        WHERE
               sd.referenced_major_id <> sd.object_id     
        )
        SELECT DISTINCT
               DependentObjectName
        FROM   
               DependentObjectCTE c
END

建立完成後，為了讓這個SP可以在任何一個資料庫中使用，請使用sp_ms_marksystemobject來將這個SP註冊為系統物件。

EXECUTE sp_ms_marksystemobject 'sp_FindDependecies'

接下來，您就可以透過它來找物件的相依性。

exec sp_FindDependencies 'employees'