Часто случается, что нам в хранимках БД необходимо найти упоминания о каких-либо константах, которые, например, собираетесь удалить. И будет хорошо, если вы помните, где именно их использовали, а что если нет?
Так вот, далее я приведу пример нескольких запросов поиска по телу хранимок, триггеров и т.д.
Первый запрос осуществляет поиск по телу функций или процедур:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your text for search%'
--AND ROUTINE_TYPE='PROCEDURE'
--OR ROUTINE_TYPE='FUNCTION'
Следующий запрос делает выборку из всех объектов, существующих в БД:
SELECT [type], [name], OBJECT_DEFINITION([object_id])
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, а также для всех хранимых процедур в базе данных.
SELECT DISTINCT so.name, sc.TEXT
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Your text for search%'
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
По просьбе Артёма написана процедура, возвращающая список объектов (название и тип), содержащих указанный текст.
CREATE PROCEDURE [dbo].[tsp_GetObjectsContainingText]
(
@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. Ниже её реализация:
CREATE FUNCTION [dbo].[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
