일부 표에서 특정 열을 참조하는 모든 저장 프로시저 찾기

일부 표에서 특정 열을 참조하는 모든 저장 프로시저 찾기

테이블에 예기치 않게 변경된 값이 있습니다.문제의 칼럼은 다음과 같습니다.CreatedDate항목이 생성될 때 설정되지만 저장 프로시저에 의해 변경되고 있습니다.

제가 어떤 종류의 것을 쓸 수 있을까요?SELECT내 테이블에서 이 열을 참조하는 모든 프로시저 이름을 가져오시겠습니까?

한 가지 옵션은 스크립트 파일을 만드는 것입니다.

데이터베이스 -> 작업 -> 스크립트 생성을 마우스 오른쪽 버튼으로 클릭합니다.

그런 다음 저장 프로시저를 모두 선택하고 모든 sp를 사용하여 스크립트를 생성할 수 있습니다.그래서 당신은 거기서 참조를 찾을 수 있습니다.

또는

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

원본 SQL SERVER – 저장 프로시저에 사용된 열 찾기 – 저장 프로시저에서 열 이름 검색

특정 열만 사용하여 저장 프로시저를 가져오려면 다음 쿼리를 사용할 수 있습니다.

SELECT DISTINCT Name
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%CreatedDate%';

특정 테이블 열을 사용하여 저장 프로시저를 가져오려면 아래 쿼리를 사용할 수 있습니다.

SELECT DISTINCT Name 
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_name%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%';

ApexSQL Search를 사용할 수 있으며, 무료 SSMS 및 Visual Studio 추가 기능이며 특정 테이블 열을 참조하는 모든 개체를 나열할 수 있습니다.또한 테이블과 뷰에 저장된 데이터를 찾을 수 있습니다.결과를 쉽게 필터링하여 열을 참조하는 특정 데이터베이스 개체 유형을 표시할 수 있습니다.

고지 사항:저는 ApexSQL에서 지원 엔지니어로 일하고 있습니다.

에 포함된 시스템 보기를 사용하여 하나의 스크립트로 테이블, 보기 및 (암호화되지 않은) 저장 프로시저를 검색할 수 있습니다.데이터베이스의 모든 곳에서 필드 이름을 검색해야 하기 때문에 얼마 전에 이러한 스크립트를 개발했습니다.

아래 스크립트는 먼저 검색 중인 열 이름이 포함된 테이블/뷰를 나열한 다음 열이 발견된 저장 프로시저 소스 코드를 나열합니다."BASE TABLE", "VIEW", "PROCEDURE"를 구분하는 하나의 테이블에 결과를 표시하고 (선택적으로) 두 번째 테이블에 소스 코드를 표시합니다.

DECLARE @SearchFor nvarchar(max)='%CustomerID%' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code

-- tables
if (@SearchSP=1) begin  
  (
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
            t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor 
  union
  select '['+routine_Schema+'].['+routine_Name+']' [schema_object], 
         'PROCEDURE' as table_type from information_schema.routines
  where routine_definition like @SearchFor
        and routine_type='procedure'
  )
  order by table_type, schema_object
end else begin
  select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object], 
         t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor 
  order by c.table_Name, c.column_name
end     
-- stored procedure (source listing)
if (@SearchSP=1) begin      
    if (@DisplaySPSource=1) begin
      select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition 
      from information_schema.routines
      where routine_definition like @SearchFor
      and routine_type='procedure'
      order by routine_name
    end
end

쿼리를 실행할 경우 "텍스트로 결과" 옵션을 사용합니다. 그런 다음 "찾기"를 사용하여 결과 집합에서 검색 텍스트를 찾을 수 있습니다(긴 소스 코드에 유용).

설정할 수 있습니다.@DisplaySPSource로.0SP 이름만 표시하고 SP가 아닌 테이블/뷰만 찾는 경우 설정할 수 있습니다.@SearchSP0.

예제 결과(찾기)CustomerIDNorthwind 데이터베이스에서 결과가 LinqPad를 통해 표시됨):

이 스크립트를 테스트 보기로 확인했습니다.dbo.TestOrders그리고 그것은 발견했습니다.CustomerID이 관점에서는 비록c.*는 되었습니다용사에 되었습니다.SELECT문 ((선행표표))Customers에는 를포니다가 되어 있습니다.CustomerID따라서 보기에 이 열이 표시됩니다.

---

LinqPad 사용자를 위한 참고 사항:C#에서 다음을 사용할 수 있습니다.dc.ExecuteQueryDynamic(sqlQueryStr, new object[] {... parameters ...} ).Dump();그리고 다음과 같은 매개 변수를 가집니다.@p0...@pn쿼리 문자열 내부에 있습니다.그런 다음 정적 확장 클래스를 작성하고 내 확장에 저장하여 LinkPad 쿼리에 사용할 수 있습니다.데이터 컨텍스트는 쿼리 창에서 다음과 같이 전달될 수 있습니다.DataContextBase dc 변수, 즉 매변 수를 통해즉개, 즉.public static void SearchDialog(this DataContextBase dc, string searchString = "%") 클래스6의 , "LinqPad 6")입니다.DataContext그런 다음 위의 SQL 쿼리를 매개 변수가 있는 문자열로 다시 작성하여 C# 컨텍스트에서 호출할 수 있습니다.

이것을 시도해 보세요.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%CreatedDate%'
GO

또는 모든 절차의 스크립트를 생성하고 여기서 검색할 수 있습니다.

저도 같은 문제가 있었고 마이크로소프트가 종속성을 보여주는 시스템 테이블을 가지고 있다는 것을 발견했습니다.

SELECT 
    referenced_id
    , referenced_entity_name AS table_name
    , referenced_minor_name as column_name
    , is_all_columns_found
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT'); 

그리고 이것은 두 가지 모두에서 작동합니다.Views그리고.Triggers.

모든 개체에서 검색

SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'ColumnName' + '%'
GO

저장 프로시저에서만 검색

SELECT DISTINCT OBJECT_NAME(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%'
GO

SELECT *
FROM   sys.all_sql_modules
WHERE  definition LIKE '%CreatedDate%'

아래 쿼리를 사용하여 값을 식별할 수 있습니다.그러나 암호화된 저장 프로시저의 결과는 제공되지 않습니다.

SELECT DISTINCT OBJECT_NAME(comments.id) OBJECT_NAME
    ,objects.type_desc
FROM syscomments comments
    ,sys.objects objects
WHERE comments.id = objects.object_id
    AND TEXT LIKE '%CreatedDate%'
ORDER BY 1

언급URL : https://stackoverflow.com/questions/19539696/find-all-stored-procedures-that-reference-a-specific-column-in-some-table