반응형
/********************************************************************************************
-- Title : [2k8] 참조 의존성 개체 확인 시스템 뷰
-- Reference : bol
-- Key word : reference 종속 참조 개체
********************************************************************************************/

-- 특정 테이블에 종속된 SP 찾기(SQL2k5)
SELECT OBJECT_NAME(id) "PROCEDURE", OBJECT_NAME(depid) "REFERENCED_TABLE"
FROM sysdepends WHERE depid = OBJECT_ID('Production.Product');


-- 해당 테이블에 관련된 모든 종속 개체 보기(SQL2k8)
USE AdventureWorks;
GO
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name,
    o.type_desc AS referencing_desciption,
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name,
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.OBJECT_ID
WHERE referenced_id = OBJECT_ID(N'Production.Product');
GO

-- 해당 개체가 참조하는 스키마 보기(SQL2k8)
USE AdventureWorks;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
    referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO
 

 
반응형

+ Recent posts