반응형
다음을 포함하는 저장 프로시저를 찾으려면 어떻게 해야 합니까?
SQL Server 2008에서 데이터베이스 필드 이름 또는 변수 이름을 포함하는 저장 프로시저를 검색해야 합니다.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Foo%'
AND ROUTINE_TYPE='PROCEDURE'
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'
무료 Red-Gate SQL Search 툴 복사본을 가져와 SQL Server에서 검색을 시작하십시오. :- )
이것은 매우 유용하고 훌륭한 도구입니다.그렇습니다! 어떤 용도로도 전혀, 전혀 무료로 사용할 수 있습니다.
카시프의 대답을 받아들였고 조합이 다 같이 했어이상하게도, 때때로 나는 어느 한 선택에서 결과를 찾았지만 다른 선택에서는 찾지 못했다.그래서 안전을 위해서, 저는 무언가를 찾을 때 이 세 가지를 다 뛰어요.이것이 도움이 되기를 바랍니다.
DECLARE @SearchText varchar(1000) = 'mytext';
SELECT DISTINCT SPName
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS T
ORDER BY T.SPName
ApexSQL에서 ApexSQL Search - SSMS plug-in을 사용해 볼 수도 있습니다.
스키마도 필요한 경우:
SELECT DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text]
FROM syscomments AS c
INNER JOIN sys.objects AS o ON c.id = o.[object_id]
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE text LIKE '%foo%'
ORDER BY SCHEMA_NAME(o.schema_id),o.name
먼저 사용자 자격 증명과 올바른 데이터베이스 컨텍스트에서 쿼리를 실행하고 있는지 확인하십시오.
USE YOUR_DATABASE_NAME;
그렇지 않으면 sys.procedures는 아무것도 반환하지 않습니다.이제 다음과 같이 쿼리를 실행합니다.
select * from sys.procedures p
join sys.syscomments s on p.object_id = s.id
where text like '%YOUR_TEXT%';
또 다른 옵션은 INFORMATION_SCHEMA를 사용하는 것입니다.루틴ROTENAL_DEFINITION은 루틴의 제한된 수(즉, 처음 4000자)만 포함할 수 있습니다.
select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)에서 테스트했습니다.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FieldName%'
AND ROUTINE_TYPE='PROCEDURE'
위의 예시를 사용해 보았지만 4000자를 넘지 않아 조금 수정하여 스토어드 프로시저 정의 전체를 얻을 수 있었습니다.아래 업데이트된 스크립트를 참조하십시오.
SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) TEXT
FROM SYSCOMMENTS AS C
INNER JOIN SYS.OBJECTS AS O ON C.ID = O.[OBJECT_ID]
INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%FOO%'
ORDER BY SCHEMA_NAME(O.SCHEMA_ID), O.NAME
SQL Server 2000보다 새로운 SQL Server의 경우:
SELECT object_name = OBJECT_NAME(sm.object_id), 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 '%searchString%'
ORDER BY o.type, o.name, o.object_id
SQL Server 2000에서 문제가 발생한 경우 sql_modules 테이블이 존재하지 않으므로 syscomments를 사용하면 4000자를 초과하는 저장 프로시저에 대해 여러 개의 레코드를 얻을 수 있습니다.다만, 이러한 레코드는 같은 c.number 필드를 가지고 있기 때문에, 완전한 저장 프로시저 텍스트를 취득할 수 있습니다.
Select o.id, c.number, o.name, c.text
from syscomments c
inner join sysobjects o on o.id = c.id
where c.encrypted = 0 and o.type = 'P'
and c.id in
(Select id from syscomments where text like '%searchtext%')
order by objecttype, o.name, o.id, c.number, c.colid
select * from sys.system_objects
where name like '%cdc%'
다음은 Losbear를 사용하지만 스키마 이름도 출력하도록 수정한 Union 버전입니다.
DECLARE @SearchText varchar(1000) = 'searchtext';
SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name'
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS sp
CROSS APPLY (select SPECIFIC_SCHEMA from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_NAME = sp.SPName) sc
ORDER BY 1
텍스트 또는 문자열을 포함하는 스토어드 프로시저를 검색하는 방법
저장 프로시저에서 텍스트 또는 문자열을 찾아야 하는 경우가 많습니다.다음은 포함된 텍스트를 찾기 위한 쿼리입니다.
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Text%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
상세한 것에 대하여는, 다음의 URL 를 참조해 주세요.
http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string
다음 코드를 사용해 보십시오.
DECLARE @SearchText NVARCHAR(2000) = N'YourText';
SELECT DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text] AS MainTextProc
FROM sys.syscomments AS c
INNER JOIN sys.objects AS o ON c.id = o.[object_id]
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE c.[text] LIKE '%'+ @SearchText +'%'
ORDER BY SCHEMA_NAME(o.schema_id),o.[name]
SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'
이는 SQL 2012에서 "Company Specialization"과 같은 이름을 가진 모든 저장 프로시저를 선택하기 위해 작성한 것입니다.
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'`enter code here`
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%' + @Text + '%' and
o.xtype = case when @Type IS NULL then o.xtype else @Type end
ORDER BY Location, ObjectName
END
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''
그러면 모든 데이터베이스의 모든 저장 프로시저가 검색됩니다.이것은 긴 절차에도 효과가 있습니다.
SELECT s.name + '.' + o.name ProcedureName
, c.text ProcedureSteps
FROM sys.syscomments c
INNER JOIN
sys.objects o
ON
c.id = o.object_id
INNER JOIN
sys.schemas s
ON
o.schema_id = s.schema_id
WHERE o.type = 'P'
AND c.text LIKE N'%XXXX%'
ORDER BY s.name + '.' + o.name
, c.colid
이 쿼리는 저장 프로시저 내에서 "XXXX"가 참조되는 저장 프로시저의 이름과 내용을 반환합니다.
이것은 특정 테이블/뷰/프로시저를 참조하는 프로시저를 찾을 때 유용하게 종료됩니다.
SP에서 텍스트를 찾기 위한 저장 프로시저..{Dinesh Baskaran} 트렌디 글로벌 시스템 pvt ltd
create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint '
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%' + @Text + '%' and
o.xtype = case when @Type IS NULL then o.xtype else @Type end
ORDER BY Location, ObjectName
END
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Name%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
이것을 시험해 보세요...
저는 이 대본을 씁니다.XML 코멘트를 노란색 배경에 검은색 텍스트로 표시하도록 변경하면 결과의 xml 열에서 원하는 텍스트를 강조 표시할 수 있습니다(도구 -> 옵션 -> 환경 -> 글꼴 및 색상 [표시 항목: XML 코멘트]).
---------------------------------------------
-------------- Start FINDTEXT ----------
---------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = 'the text you''re looking for'
DECLARE @OverrideSearchStringWith VARCHAR(MAX)
--#############################################################################
-- Use Escape chars in Brackets [] like [%] to find percent char.
--#############################################################################
DECLARE @ReturnLen INT
SET @ReturnLen = 50;
with lastrun
as (select DEPS.OBJECT_ID
,MAX(last_execution_time) as LastRun
from sys.dm_exec_procedure_stats DEPS
group by deps.object_id
)
SELECT OL.Type
,OBJECT_NAME(OL.Obj_ID) AS 'Name'
,LTRIM(RTRIM(REPLACE(SUBSTRING(REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), ''), CHARINDEX(@SearchString, OBJECT_DEFINITION(OL.Obj_ID)) - @ReturnLen, @ReturnLen * 2), @SearchString, ' ***-->>' + @SearchString + '<<--*** '))) AS SourceLine
,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')), '&', '(A M P)'), '<', '(L T)'), '>', '(G T)'), @SearchString, '<!-->' + @SearchString + '<-->') AS XML) AS 'Hilight Search'
,(SELECT [processing-instruction(A)] = REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')
FOR
XML PATH('')
,TYPE
) AS 'code'
,Modded AS Modified
,LastRun as LastRun
FROM (SELECT CASE P.type
WHEN 'P' THEN 'Proc'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trig'
ELSE 'Func'
END AS 'Type'
,P.OBJECT_ID AS OBJ_id
,P.modify_Date AS modded
,LastRun.LastRun
FROM sys.Objects P WITH (NOLOCK)
LEFT join lastrun on P.object_id = lastrun.object_id
WHERE OBJECT_DEFINITION(p.OBJECT_ID) LIKE '%' + @SearchString + '%'
AND type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
-- AND lastrun.LastRun IS NOT null
) OL
OPTION (FAST 10)
---------------------------------------------
---------------- END -----------------
---------------------------------------------
---------------------------------------------
언급URL : https://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text
반응형
'programing' 카테고리의 다른 글
| Microsoft SQL Server 로그인 오류: 18456 (0) | 2023.04.07 |
|---|---|
| 백업을 복원할 때 모든 활성 연결을 끊으려면 어떻게 해야 합니까? (0) | 2023.04.07 |
| SQL Server의 기존 테이블에 기본값 열 추가 (0) | 2023.04.07 |
| 데이터베이스에 보기를 작성하는 이유는 무엇입니까? (0) | 2023.04.07 |
| Link-to-Sql: 여러 왼쪽 외부 결합 (0) | 2023.04.07 |