Часто случается, что нам в хранимках БД необходимо найти упоминания о каких-либо константах, которые, например, собираетесь удалить. И будет хорошо, если вы помните, где именно их использовали, а что если нет?
Так вот, далее я приведу пример нескольких запросов поиска по телу хранимок, триггеров и т.д.
Первый запрос осуществляет поиск по телу функций или процедур:
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your text for search%'
--AND ROUTINE_TYPE='PROCEDURE'
--OR ROUTINE_TYPE='FUNCTION'
Следующий запрос делает выборку из всех объектов, существующих в БД:
FROM sys.objects
WHERE [type] IN ('P','TR','FN') AND OBJECT_DEFINITION([object_id]) LIKE N'%Your text for search%';
Но sys.objects не показывает триггеры DDL, так как они не принадлежат области схемы. Все триггеры (DML и DDL) найдены в таблице sys.triggers, которая поддерживает смешанные правила имен для различных видов триггеров. (Материал с MSDN)
Какие существуют типы объектов:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
И последний способ - это поиск по таблице syscomments, которая содержит записи для всех представлений, правил, значений по умолчанию, триггеров, ограничений CHECK и DEFAULT, а также для всех хранимых процедур в базе данных.
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Your text for search%'
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
По просьбе Артёма написана процедура, возвращающая список объектов (название и тип), содержащих указанный текст.
(
@Text VARCHAR(Max),
@Types VARCHAR(250) = 'P,TR,FN,TF'
)
AS
BEGIN
SET NOCOUNT ON;
SELECT [name], [type_desc]
FROM sys.objects
WHERE EXISTS (
SELECT d.Part FROM dbo.fn_ParseString(@Types, ',') d
WHERE d.Part COLLATE Cyrillic_General_CI_AI = [type] COLLATE Cyrillic_General_CI_AI
) AND OBJECT_DEFINITION([object_id]) LIKE '%' + @Text + '%';
END
2-й входящий параметр Types по умолчанию = 'P,TR,FN,TF'.
Также для работы процедуры необходима функция fn_ParseString. Ниже её реализация:
@ParseString nvarchar(4000), @Delimiter nvarchar(100))
returns @Result TABLE (
[Line] int NOT NULL identity(1, 1),
[Part] nvarchar(4000)
)
AS
begin
declare @Part nvarchar(4000)
declare @INDEX int
SET @Delimiter = '%' + @Delimiter + '%'
while (@ParseString > '')
begin
SET @INDEX = patindex(@Delimiter, @ParseString)
IF (@INDEX > 0)
begin
SET @Part = substring(@ParseString, 1, @INDEX -1)
INSERT INTO @Result SELECT rtrim(ltrim(@Part))
SET @ParseString = substring(@ParseString, @INDEX +1, len(@ParseString))
end else
begin
SET @Part = substring(@ParseString, 1, len(@ParseString))
INSERT INTO @Result SELECT rtrim(ltrim(@Part))
break
end
end
RETURN
end
Максим, спасибо за ценную информацию!
Я бы добавил в список поиска также и
"Караша Максим Юрьевич" написал:
TF = SQL table-valued-function
т.к. они нередко применяются при разработке. И оформить это в виде готового решения - хранимой процедуры, которая на вход принимает текст для поиска, а на выходе получаем список объектов.
Да, присоединяюсь к пожеланиям Артёма.
Нужно реализовать это всё в базовом функционале TSAdmin - в окне Greap Search!
Да, чтоб разработчики этим пользовались, лучше разместить в уже известном месте, в GREP-search
Для Оракла не нашёл корректного поиска.
Вот как вариант:
select DISTINCT user_objects.object_name, user_objects.object_type from user_objects JOIN sys.user_source ON sys.user_source.name = user_objects.object_name WHERE user_objects.object_type in ('PROCEDURE','FUNCTION','TRIGGER') AND sys.user_source.Text like '%text%'
Но недостатки запроса в том, что в таблице sys.user_source тело хранимок разбито на много записей, содержащихся в нескольких рядках.
Например небольшой цикл может занимать несколько записей:
1. BEGIN
2. LOOP
3. -- do something with the data
4. END LOOP;
И уже поиск по условию
sys.user_source.Text like '%BEGIN%LOOP%'
ничего не вернёт.
Может кто знает более универсальный вариант?