[KB]How to find dependency in SQL server 2005

2008-06-03

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

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

   1:  USE master

   2:  GO

   3:  CREATE PROCEDURE sp_FindDependencies

   4:  (

   5:          @ObjectName SYSNAME,

   6:          @ObjectType VARCHAR(5) = NULL

   7:  )

   8:  AS

   9:  BEGIN

  10:      DECLARE @ObjectID AS BIGINT

11:

  12:          SELECT TOP(1) @ObjectID = object_id

  13:          FROM sys.objects

  14:          WHERE name = @ObjectName

  15:          AND type = ISNULL(@ObjectType, type)

16:

  17:      SET NOCOUNT ON ;

18:

  19:        WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)

  20:          AS

  21:          (

  22:          SELECT DISTINCT

  23:                 sd.object_id,

  24:                 OBJECT_NAME(sd.object_id),

  25:                 ReferencedObject = OBJECT_NAME(sd.referenced_major_id),

  26:                 ReferencedObjectID = sd.referenced_major_id

  27:          FROM

  28:                 sys.sql_dependencies sd

  29:                 JOIN sys.objects so ON sd.referenced_major_id = so.object_id

  30:          WHERE

  31:                 sd.referenced_major_id = @ObjectID

  32:          UNION ALL

  33:          SELECT

  34:                 sd.object_id,

  35:                 OBJECT_NAME(sd.object_id),

  36:                 OBJECT_NAME(referenced_major_id),

  37:                 object_id

  38:          FROM

  39:                 sys.sql_dependencies sd

  40:              JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID

  41:          WHERE

  42:                 sd.referenced_major_id <> sd.object_id

  43:          )

  44:          SELECT DISTINCT

  45:                 DependentObjectName

  46:          FROM

  47:                 DependentObjectCTE c

  48:  END

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

EXECUTE sp_ms_marksystemobject 'sp_FindDependecies'

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

exec sp_FindDependencies 'employees'

Response.Write("Hello LOLOTA")