Table을 참조하는 object를 syscomments에서 DMV로 바꾸어 보아요

sys.dm_sql_referencing_entities DMV는 지정된 엔터티를 참조하는 현재 데이터베이스에 있는 다음과 같은 엔터티 유형의 행을 반환합니다 

참고 링크

• http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=KO-KR&k=k(SYS.DM_SQL_REFERENCING_ENTITIES_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(DevLang-TSQL)&rd=true
• http://sqlblog.com/blogs/tamarick_hill/default.aspx
• http://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies

   

사용가능 버전 SQL2008, SQL2008R2+, SQL2012   

지정된 엔터티를 참조하는 현재 데이터베이스에 있는 다음과 같은 엔터티 유형을 알 수 있습니다.

• 스키마 바운드 또는 비스키마 바운드 엔터티
• 데이터베이스 수준 DDL 트리거
• 서버 수준 DDL 트리거

   

이 DMV는 언제 사용할까요? 사실 오늘 저도 처음 사용해 보았어요. 테이블 이름을 변경했지만 관련된 프로시져나 뷰가 어디서 사용하는지 찾는 방법을 이전에는 syscomments라는 것을 사용했습니다. 상세 정보는 여기를 참고하세요. SQL2008 이후 버전에서는 sys.dm_sql_referencing_entities이 DMV를 사용하면 됩니다.   

   

테스트 용 table, procedure 생성

CREATE TABLE [dbo].[tbl1](
	[Name] [char](10) NOT NULL,
	[Param] [float] NOT NULL
)
GO

create index idx_tbl1_name on dbo.tbl1 ( name );


CREATE TABLE [dbo].[tbl2](
	[name] [char](10) NOT NULL,
	[upper] [varchar](256) NOT NULL
)
GO

CREATE PROCEDURE dbo.UspGetParam
AS	
	SELECT 
	*
	FROM [dbo].[tbl1]
GO
CREATE PROCEDURE dbo.UspGetREParam
AS	
	SELECT 
	*
	FROM [dbo].[tbl2]
GO

   

SQL2000 버전

-- SQL 2000
-- 뷰, 규칙, 기본값, 트리거, CHECK 제약 조건, DEFAULT 제약 조건 및 저장 프로시저에 대한 항목
SELECT A.name ProcedureName
FROM sysobjects A, syscomments B
WHERE A.id = B.ID
	AND B.text LIKE '%' + @objNm + '%';	

   

SQL2008+ 버전

-- SQL 2008+
-- 위의 내용 포함 데이타베이스 수준 DLL 트리거, 서버 수준의 DLL 트리거
with cte_object
as
(
	select 
		objectnm = t2.name + '.' + t1.name, type_desc
	from sys.tables t1
		inner join sys.schemas t2
			ON t1.schema_id = t2.schema_id
	where t1.name LIKE @objNm + '%'
) 
select *
from cte_object t1
	cross apply sys.dm_sql_referencing_entities(t1.objectnm, 'OBJECT') t2 ;
Go