Мне нужно найти SQL Server 2008 для хранимых процедур, содержащих, где возможно имя поля базы данных или имя переменной.
Как найти хранимую процедуру, содержащую <text>?
Ответ 1
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%'
Ответ 2
Возьмите себе копию бесплатного Red-Gate SQL Search и начните поиск в SQL Server!: -)
Это отличный и очень полезный инструмент, и YES! он полностью, абсолютно БЕСПЛАТНО для любого использования.
Ответ 3
Вы также можете попробовать ApexSQL Search - бесплатный плагин SSMS от ApexSQL.
Ответ 4
Я взял ответ Кашифа и объединил их всех. Странно, иногда, я нашел результаты в одном из вариантов, но не в другом. Поэтому, чтобы быть в безопасности, я запускаю все 3, когда я что-то ищу. Надеюсь, это поможет:
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
Ответ 5
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FieldName%'
AND ROUTINE_TYPE='PROCEDURE'
Ответ 6
Если вам нужна также схема:
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
Ответ 7
Я попробовал выше пример, но он не показывал более 4000 символов, а затем немного изменил его и смог получить определение всей хранимой процедуры. См. Обновленный script ниже -
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
Ответ 8
Сначала убедитесь, что вы выполняете запрос под учетными данными пользователя, а также в правильном контексте базы данных.
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.ROUTINES.ROUTINE_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)
Ответ 9
select * from sys.system_objects
where name like '%cdc%'
Ответ 10
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
Ответ 11
SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'
Это то, что я написал в SQL 2012, чтобы выбрать все хранимые процедуры, в которых имя типа "CompanySpecialisation"
Ответ 12
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
Ответ 13
Сохраненная процедура поиска текста в SP.. {Dinesh Baskaran} Trendy Global Systems 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
Ответ 14
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Name%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
Попробуйте это.....
Ответ 15
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''
Это будет искать во всех хранимых процедурах всех баз данных. Это также будет работать для длительных процедур.
Ответ 16
Я использую этот script. Если вы измените свои 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 -----------------
---------------------------------------------
---------------------------------------------
Ответ 17
Вот версия union, которая использует Losbear, но изменена так, что она также выводит имя схемы:
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
Ответ 18
Как найти сохраненную процедуру, содержащую текст или строку
Много раз нам нужно найти текст или строку в хранимой процедуре. Вот запрос, чтобы найти содержащий текст.
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
Ответ 19
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" ссылается в хранимой процедуре.
Это полезно при поиске процедур, которые ссылаются на конкретную таблицу/представление/процедуру