programing

SQL Server에서 열이 참조되는 모든 위치를 어떻게 찾을 수 있습니까?

i4 2023. 7. 15. 09:41
반응형

SQL Server에서 열이 참조되는 모든 위치를 어떻게 찾을 수 있습니까?

비교적 큰 데이터베이스 내에서 전체 스키마 내에서 열이 참조되는 모든 위치(SP, 함수, 테이블, 트리거 등)를 확인하고 싶습니다.텍스트 검색만 수행하고 싶지는 않습니다. 이렇게 하면 다른 테이블에서 비슷한 이름의 열을 찾을 수 있기 때문입니다.

제가 이걸 어떻게 할 수 있는지 아는 사람 있나요?SQL Server 2008을 사용합니다.

경고:비록 이것이 텍스트 검색 방법이지만, 제가 공유할 스크립트는 저에게 많은 시간을 절약해 주었습니다.내부를 검색합니다.

  • 스칼라 함수
  • 테이블 값 함수
  • 저장 프로시저
  • 견해
  • 트리거

나는 그것이 나에게 효과가 있도록 하기 위해 그것을 지정할 필요가 있었습니다.

SELECT
    sys.objects.object_id, 
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

출력은 다음과 같습니다.

Output

업데이트: 특정 테이블, SP 등을 검색해야 하는 경우 보다 전문적인 쿼리를 사용할 수 있습니다.

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME VARCHAR(100) = 'MY_OBJECT';

SELECT
    sys.objects.object_id,
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    (
           '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
    )
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

추신: 두 쿼리 모두 댓글 내부도 검색합니다.

나는 이 질문을 시도했고 그것은 괜찮은 것 같습니다.

select 
obj.type REFERENCING_OBJECT_TYPE
 ,SCHEMA_NAME(obj.schema_id) REFERENCING_OBJECT_SCHEMA
 ,obj.name                  REFERENCING_OBJECT_NAME
from sysdepends x
INNER JOIN sys.objects obj ON x.id  = obj.object_id
where depid = object_id('yourSchema.yourTable')
and col_name(depid, depnumber) = 'yourColumn'

가장 좋은 방법은 이 문서에 설명되어 있습니다.

샘플:

SELECT OBJECT_NAME (referencing_id),
              referenced_database_name, 
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE OBJECT_NAME(d.referenced_id) = 'TableName'
      AND OBJECT_DEFINITION (referencing_id)  LIKE '%ColumnName%'
ORDER BY OBJECT_NAME(referencing_id);

임시 테이블에 대한 참조가 있는지 여부를 표시하지 않습니다.
예

create table dbo.TableName (columnName int  )
go
create procedure dbo.ProcedureOne 
as 

    update dbo.TableName  set columnName = 1

go

create or alter procedure dbo.ProcedureTwo 
as 

    create table #test (dd int)
    update 
        t1
    set 
        t1.columnName = 1
    from 
        dbo.TableName   t1
    inner join
        #test t2 on t1.columnName = t2.dd



SELECT 
        1 As Level
        ,t2.type AS ObjectType
        ,CAST(CONCAT(SCHEMA_NAME(t2.schema_id),'.',t2.name) AS varchar(256)) AS RefName
        ,CAST('' AS varchar(256)) AS RefBy
    FROM 
        SYSDEPENDS t1
    LEFT JOIN 
        SYS.OBJECTS t2 ON t1.id  = t2.OBJECT_ID
    WHERE 
        t1.depid = OBJECT_ID('dbo.TableName')
        AND COL_NAME(t1.depid, t1.depnumber) = 'columnName'

예

Luv가 말했듯이 이것은 오래된 질문이지만 저는 도움이 될 수 있는 두 가지 해결책을 더 찾았습니다.

지정된 개체에서 참조된 모든 개체와 열을 찾는 sys.dm _sql_composed_column 시스템 개체를 사용하고 있습니다.다음 쿼리를 사용할 수 있습니다.

SELECT DISTINCT 
referenced_schema_name AS SchemaName, 
referenced_entity_name AS TableName, 
referenced_minor_name  AS ColumnName
FROM sys.dm_sql_referenced_entities ('yourrefencingobject', 'OBJECT'); 
GO

다음과 같은 결과를 제공합니다.

enter image description here

이 개체의 단점은 참조 개체를 지정해야 한다는 것입니다.

또는 다음과 같은 검색을 수행합니다.

SELECT DISTINCT object_name(id) 
FROM AdventureWorks2012.dbo.syscomments (nolock) 
WHERE text like '%BusinessEntityID%'

다음과 같은 결과를 제공합니다.

enter image description here

또한 이 기사에서 사용할 수 있는 다음 SP를 찾았지만 제대로 테스트하지 않았습니다.

> DECLARE    @string varchar(1000),   @ShowReferences char(1)
> 
> SET @string = 'Person.Person.BusinessEntityID' --> searchstring
> 
> SET @ShowReferences = 'N'
> /****************************************************************************/ /*                                                                    
> */ /* TITLE:   sp_FindReferences                                               */ /*                                                                          */ /* DATE:    18 February, 2004                                               */ /*                                                                          */ /* AUTHOR:  WILLIAM MCEVOY                                                  */ /*                                                                          */ /****************************************************************************/ /*                                                                    
> */ /* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /*                                                       
> */ /****************************************************************************/ set nocount on
> 
> declare @errnum         int         ,
>         @errors         char(1)     ,
>         @rowcnt         int         ,
>         @output         varchar(255)
> 
> select  @errnum         = 0         ,
>         @errors         = 'N'       ,
>         @rowcnt         = 0         ,
>         @output         = ''        
> 
> /****************************************************************************/ /* INPUT DATA VALIDATION                                              
> */ /****************************************************************************/
> 
> 
> /****************************************************************************/ /* M A I N   P R O C E S S I N G                                      
> */ /****************************************************************************/
> 
> -- Create temp table to hold results DECLARE @Results table (   Name        varchar(55),   Type        varchar(12),   DateCreated datetime,  
> ProcLine    varchar(4000) )
> 
> 
> IF (@ShowReferences = 'N') BEGIN   insert into @Results   select
> distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END ELSE BEGIN   insert into @Results  
> select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = text
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select 
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END
> 
> IF (@ShowReferences = 'N') BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated
>     from @Results
>     order by 2,1 END ELSE BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated,
>          ProcLine
>     from @Results
>     order by 2,1 END

이것이 도움이 되길 바랍니다.

저는 sys 사용을 피하고 싶습니다.이 기능은 향후 버전의 Microsoft SQL Server에서 제거될 예정이므로 sql_dependencies입니다.또한 모든 객체가 암호화되어 있기 때문에 OBJECT_DEFINITION 함수를 사용할 수 없습니다.그래서 저는 다음과 같은 질문을 생각해냈습니다. 매우 단순하고 저에게 잘 적용되어 함수에 포함시키기도 했습니다.

-- =============================================
-- Description: Gets all the stored procedures, functions and triggers referencing the specified column.
-- Example:     SELECT * FROM dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN(N'dbo', N'MY_TABLE', N'MY_COLUMN');
-- =============================================
CREATE FUNCTION dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN 
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT R.referencing_schema_name + N'.' + R.referencing_entity_name AS referencing_entity_name
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN ('FN'/*SQL scalar function*/, 'IF'/*SQL inline table-valued function*/, 'TF'/*SQL table-valued-function*/, 'P'/*SQL Stored Procedure*/, 'TR' /*SQL DML trigger*/)
          AND EXISTS(SELECT 1 FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName);

GO

안녕하세요, 비록 이것은 오래된 게시물이지만, 저는 위의 몇 가지 팁을 결합할 수 있었습니다. 아래와 같은 것을 얻었고, 그것은 저에게 도움이 되었습니다.제가 이것을 만든 이유는 제가 많은 테이블에 있어서 명확한 출력을 제공하지 못했기 때문입니다.

SELECT 
      SCHEMA_NAME(schema_id)+'.'+[name] as objectname
      ,type_desc
     ,referenced_schema_name AS SchemaName
,referenced_entity_name AS TableName
,referenced_minor_name  AS ColumnName
  FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e

  where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION'
,'SQL_SCALAR_FUNCTION'
,'SQL_INLINE_TABLE_VALUED_FUNCTION'
,'SQL_STORED_PROCEDURE'
,'SQL_TABLE_VALUED_FUNCTION'
,'SQL_TRIGGER'
,'VIEW')
and name !='sp_upgraddiagrams'
and referenced_entity_name  = 'table name'
and referenced_minor_name = 'columnname'

다음은 @alex의 TV-UDF에서 보기를 포함하도록 약간 수정한 내용입니다.

/*
    Source: https://stackoverflow.com/a/47775531/852956
    Gets all the stored procedures, functions and triggers referencing the specified column.

    SELECT * FROM Utility.ft_SelectSprocFuncAndTrigrRefs(N'BrakeRotor', N'BrakeRotors', N'BrakeRotorNumber');
*/
CREATE FUNCTION Utility.ft_SelectSprocFuncAndTrigrRefs
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT QUOTENAME(R.referencing_schema_name) + N'.' + QUOTENAME(R.referencing_entity_name) AS ReferencingEntityName
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN (
                'FN'/*SQL scalar function*/,
                'IF'/*SQL inline table-valued function*/,
                'TF'/*SQL table-valued-function*/,
                'P'/*SQL Stored Procedure*/,
                'TR' /*SQL DML trigger*/
            )
          AND EXISTS(
                    SELECT 1
                    FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE
                    WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName)

    UNION SELECT QUOTENAME(VIEW_SCHEMA) + N'.' + QUOTENAME(VIEW_NAME) AS ReferencingEntityName
        FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
          AND column_name = @ColumnName
GO

오늘 이 작업을 수행해야 했고 다음과 같은 작업을 수행했습니다.

declare @obj sysname = 'schema.table';

select p.referencing_entity_name, c.*
from sys.dm_sql_referencing_entities(@obj, 'object') as p
cross apply sys.dm_sql_referenced_entities(
   concat(p.referencing_schema_name, '.', p.referencing_entity_name), 
   'object'
) as c
where c.referenced_id = object_id(@obj)
    and referenced_minor_name in ('yourColumn');

언급URL : https://stackoverflow.com/questions/1883700/in-sql-server-how-can-i-find-everywhere-a-column-is-referenced

반응형

'programing' 카테고리의 다른 글

Native SQL을 사용하는 Dutrinction Orm 2 재귀 쿼리  (0) 2023.07.15
SQL - COALESCE와 ISNULL의 차이?  (0) 2023.07.15
SQL Server에서 데이터베이스의 테이블에 읽기 및 쓰기 액세스 권한을 부여하는 T-SQL이란 무엇입니까?  (0) 2023.07.15
Capybara에서 확인란을 어떻게 확인합니까?  (0) 2023.07.15
사용자 "이름" 필드의 적절한 길이 제한은 얼마입니까?  (0) 2023.07.15

'programing'의 다른글

  • 현재글SQL Server에서 열이 참조되는 모든 위치를 어떻게 찾을 수 있습니까?

관련글

댓글

티스토리툴바