SQL Server 프로시저/트리거 내에서 텍스트를 찾는 방법

SQL Server 프로시저/트리거 내에서 텍스트를 찾는 방법

변경되는 링크 서버가 있습니다.하는 프로시저도 [10.10.100.50].dbo.SPROCEDURE_EXAMPLE이런 작업을 하는 트리거도 있습니다. 우리가 쓰는 을 다 돼요.[10.10.100.50]바꿀 수 있습니다.

SQL Server Management Studio Express에서는 Visual Studio에서 "전체 데이터베이스 검색"과 같은 기능을 찾을 수 없었습니다.특별한 sys-select를 통해 필요한 것을 찾을 수 있습니까?

다음은 시스템에서 텍스트를 찾기 위해 사용하는 절차의 일부입니다.

DECLARE @Search varchar(255)
SET @Search='[10.10.100.50]'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

다음과 같이 찾을 수 있습니다.

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'

저장 프로시저 내에 '사용자'와 같은 텍스트가 포함된 개별 저장 프로시저 이름이 나열됩니다.상세 정보

[늦은 답변이지만 유용했으면 좋겠다]

일부 저장 프로시저 및/또는 뷰가 암호화될 가능성이 있기 때문에 시스템 테이블을 사용해도 100% 정확한 결과를 얻을 수 있는 것은 아닙니다.이 경우 필요한 데이터를 얻기 위해 DAC 연결을 사용해야 합니다.

ApexSQL Search 등 암호화된 오브젝트를 쉽게 처리할 수 있는 서드파티 툴을 사용하는 것을 추천합니다.

개체가 암호화된 경우 시스템 테이블에서 텍스트 열에 null 값을 제공합니다.

-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = 'employee';

-- Get the schema name, table name, and table type for:

-- Table names
SELECT
       TABLE_SCHEMA  AS 'Object Schema'
      ,TABLE_NAME    AS 'Object Name'
      ,TABLE_TYPE    AS 'Object Type'
      ,'Table Name'  AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
 --Column names
SELECT
      TABLE_SCHEMA   AS 'Object Schema'
      ,COLUMN_NAME   AS 'Object Name'
      ,'COLUMN'      AS 'Object Type'
      ,'Column Name' AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
      SPECIFIC_SCHEMA     AS 'Object Schema'
      ,ROUTINE_NAME       AS 'Object Name'
      ,ROUTINE_TYPE       AS 'Object Type'
      ,ROUTINE_DEFINITION AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
      AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');

이 방법은 다음과 같습니다.

use [ANALYTICS]  ---> put your DB name here
GO
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where sm.definition like '%SEARCH_WORD_HERE%' collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO

링크된 서버가 변경될 때마다 저장 프로시저, 함수 및 뷰의 텍스트를 수정하는 것보다 훨씬 나은 솔루션이 있습니다.몇 가지 옵션은 다음과 같습니다.

1. 링크된 서버를 갱신합니다.된 링크 주소 등의 하여 새로운 .Finance ★★★★★★★★★★★★★★★★★」DataLinkProd뭐 그런 것들이죠.그런 다음 연결된 서버를 변경해야 할 경우 새 서버를 가리키도록 링크된 서버를 업데이트하십시오(또는 서버를 삭제한 후 다시 생성).

2. 안타깝게도 링크된 서버 또는 스키마에 대한 동의어를 작성할 수 없지만 링크된 서버에 있는 오브젝트에 대한 동의어를 만들 수 있습니다.들어 순서 「」, 「」 등입니다.[10.10.100.50].dbo.SPROCEDURE_EXAMPLE스키마를 datalinkprod , , , 「 」CREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;그런 다음 링크된 서버 이름을 받아들이는 저장 프로시저를 작성합니다.이 저장 프로시저는 리모트데이터베이스에서 모든 잠재적인 오브젝트를 쿼리하고 (재) 유사어를 만듭니다. 한 은 SP로 합니다.datalinkprod후 는 링크된 서버를 변경하기만 EXEC dbo.SwitchLinkedServer '[10.10.100.51]';순식간에 다른 링크 서버를 사용할 수 있게 됩니다.

더 많은 선택지가 있을 수 있습니다.인간이 작성한 스크립트를 변경하는 것보다 전처리, 설정 또는 간접이라는 뛰어난 기술을 사용하는 것이 좋습니다.머신에 의해 작성된 스크립트를 자동적으로 갱신하는 것도 문제 없습니다.이것은 전처리입니다.일을 수동으로 하는 것은 끔찍하다.

select text
from syscomments
where text like '%your text here%'

이것은 SQL2008에서 시도했던 것으로 모든 DB에서 한 번에 검색할 수 있습니다.

Create table #temp1 
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))

Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)

set  @searhString='firstweek'

declare db_cursor cursor for 
select dbid, [name] 
from master..sysdatabases
where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')



open db_cursor
fetch next from db_cursor into @dbid, @dbname

while (@@fetch_status = 0)
begin
    PRINT 'DB='+@dbname
    set @longstr = 'Use ' + @dbname + char(13) +        
        'insert into #temp1 ' + char(13) +  
        'SELECT @@ServerName,  ''' + @dbname + ''', Name 
        , case  when [Type]= ''P'' Then ''Procedure''
                when[Type]= ''V'' Then ''View''
                when [Type]=  ''TF'' Then ''Table-Valued Function'' 
                when [Type]=  ''FN'' Then ''Function'' 
                when [Type]=  ''TR'' Then ''Trigger'' 
                else [Type]/*''Others''*/
                end 
        , '''+ @searhString +''' FROM  [SYS].[SYSCOMMEnTS]
        JOIN  [SYS].objects ON ID = object_id
        WHERE TEXT LIKE ''%' + @searhString + '%'''

 exec (@longstr)
 fetch next from db_cursor into @dbid, @dbname
end

close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1

이거 업무용으로 쓰고 있어요.[ @TEXT ]필드에 있는 []는 생략하고 모든 것을 반환하고 싶은 것 같습니다.

카운트 없음 설정
선언 @TEXT VARCHAR(250)@SQL VARCHAR(250) 선언
선택 @TEXT='10.10.100.50'
CREATE TABLE #results (db VARCHAR(64), 오브젝트명 VARCHAR(100), xtype VARCHAR(10), 정의 텍스트)
'검색 문자열'로 @TEXT를 선택합니다.마스터 이름 선택 시 #데이터베이스 커서 선언..sysdatabases 여기서 dbid > 4DECLARE @c_dbname varchar(64)오픈 #데이터베이스FETCH #데이터베이스 IN @c_dbname한편 @@FETCH_STATUS - 1시작한다.@SQL = '#timeout에 삽입'을 선택합니다.SELECT = @SQL + 'SELECT' + @c_dbname + '' AS db, o.name, o.xtype, m.definition''@SQL = @SQL + 'FROM '+@c_dbname+.'sys.sql_debname m'을 선택합니다.@SQL = @SQL + ' 이너 조인 '+@c_dbname+'를 선택합니다.sysobjects o ON m.object_id=o.id'@SQL = @SQL + 'WHERE [ definition ]를 선택합니다.예를 들어 '%'+@TEXT+'%'EXEC(@SQL)FETCH #데이터베이스 IN @c_dbname끝.닫기 #데이터베이스할당 해제 #데이터베이스
SELECT * FROM #db, xtype, 오브젝트 이름별로 순서를 지정합니다.드롭 테이블 #결과

과거에 사용한 적이 있습니다.

• 모든 사용자 저장 프로시저에서 테이블 이름 검색
• 모든 테이블의 모든 열에 있는 SQL Server 데이터 검색 및 바꾸기

이 경우 스토어드 프로시저 전체에서 특정 문자열을 교체해야 하는 경우 첫 번째 링크가 더 적절할 수 있습니다.

퀵 검색 애드인은 SQL Server Management Studio에서 객체 이름을 검색할 때도 유용합니다.Codeplex 에서는, 일부의 개량판을 이용할 수 있습니다.또 다른 새로운 버전도 이용할 수 있고, 그 외의 편리한 추가 기능도 이용할 수 있습니다.

select 문을 사용하여 검색하면 개체 이름만 생성됩니다. 여기서 검색 키워드는 이 항목을 포함합니다.가장 쉽고 효율적인 방법은 절차/함수 스크립트를 가져와 생성된 텍스트 파일을 검색하는 것입니다.저도 이 기술을 따릅니다:) 그럼 정확하게 지적하셨군요.

다음 SQL을 사용하여 모든 데이터베이스 개체의 정의 내에서 검색할 수 있습니다.

SELECT 
    o.name, 
    o.id, 
    c.text,
    o.type
FROM 
    sysobjects o 
RIGHT JOIN syscomments c 
    ON o.id = c.id 
WHERE 
    c.text like '%text_to_find%'

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Your Text%' 

일반적인 외부 교차 참조용으로 작성했습니다.

create table #XRefDBs(xtype varchar(2),SourceDB varchar(100), Object varchar(100), RefDB varchar(100))

declare @sourcedbname varchar(100),
        @searchfordbname varchar(100),
        @sql nvarchar(4000)
declare curs cursor for
    select name 
    from sysdatabases
    where dbid>4
open curs
fetch next from curs into @sourcedbname
while @@fetch_status=0
    begin
    print @sourcedbname
    declare curs2 cursor for 
        select name 
        from sysdatabases
        where dbid>4
        and name <> @sourcedbname
    open curs2
    fetch next from curs2 into @searchfordbname
    while @@fetch_status=0
        begin
        print @searchfordbname
        set @sql = 
        'INSERT INTO #XRefDBs (xtype,SourceDB,Object, RefDB)
        select DISTINCT o.xtype,'''+@sourcedbname+''', o.name,'''+@searchfordbname+'''
        from '+@sourcedbname+'.dbo.syscomments c
        join '+@sourcedbname+'.dbo.sysobjects o on c.id=o.id
        where o.xtype in (''V'',''P'',''FN'',''TR'')
        and (text like ''%'+@searchfordbname+'.%''
          or text like ''%'+@searchfordbname+'].%'')'
        print @sql
        exec sp_executesql @sql
        fetch next from curs2 into @searchfordbname
        end
    close curs2
    deallocate curs2
    fetch next from curs into @sourcedbname
    end
close curs
deallocate curs

select * from #XRefDBs

언급URL : https://stackoverflow.com/questions/674623/how-to-find-a-text-inside-sql-server-procedures-triggers