Поиск текста в хранимой процедуре в SQL Server

Question

Поиск текста в хранимой процедуре в SQL Server

Я хочу найти текст во всех хранимых процедурах моей базы данных. Я использую ниже SQL:

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 '%[ABD]%';

Я хочу искать [ABD] во всех хранимых процедурах, включая квадратные скобки, но это не дает должного результата. Как я могу изменить свой запрос, чтобы добиться этого?

1102

sql sql-server sql-server-2008 stored-procedures system-stored-procedures

Источник

user815244 05 фев '13 в 09:30

25 ответов

Решение

Попробуйте этот запрос:

запрос

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%strHell%'

439

Источник

user2704077 21 авг '13 в 14:39

Я обычно запускаю следующее для достижения этой цели:

select distinct object_name(id) 
from syscomments 
where text like '%[ABD]%'
order by object_name(id)

62

Источник

user291270 05 фев '13 в 09:33

Вы пытались использовать некоторые сторонние инструменты для поиска? Есть несколько доступных бесплатно, которые сэкономили мне массу времени в прошлом.

Ниже приведены два дополнения SSMS, которые я использовал с хорошим успехом.

ApexSQL Search - Поиск и схемы и данных в базах данных и имеет дополнительные функции, такие как отслеживание зависимостей и многое другое...

Пакет инструментов SSMS - имеет те же функции поиска, что и предыдущий, и несколько других интересных функций. Не бесплатно для SQL Server 2012, но все еще очень доступно.

Я знаю, что этот ответ не на 100% связан с вопросами (который был более конкретным), но, надеюсь, другие найдут это полезным.

59

Источник

user2042836 05 сен '13 в 10:55

Хорошая практика для работы с SQL Server.

Сделайте процедуру сохранения ниже и установите короткую клавишу для этого, как нижнее изображение,

CREATE PROCEDURE [dbo].[Searchinall]       
(@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN
        SELECT t.name      AS Table_Name
              ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
               INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
               Table_Name
    END
END

Сейчас - установите короткий ключ, как показано ниже,

Поэтому в следующий раз, когда вы захотите найти определенный текст в любом из четырех объектов, таких как Store procedure, Views, Functions а также Tables, Вам просто нужно написать это ключевое слово и нажать короткую клавишу.

Например: я хочу найти "PaymentTable", затем написать "PaymentTable" в редакторе запросов и нажать короткую клавишу ctrl+4 - это даст вам полный результат.

45

Источник

user1156018 10 ноя '15 в 13:23

SQL Search от Redgate - отличный инструмент для этого, это бесплатный плагин для SSMS.

22

Источник

user1904792 03 ноя '13 в 07:56

Пожалуйста, примите это как "грязную" альтернативу, но это много раз спасло меня, особенно когда я не был знаком с проектом БД. Иногда вы пытаетесь найти строку во всех SP, и забываете, что некоторая связанная логика могла скрываться между функциями и триггерами, или она может быть просто сформулирована иначе, чем вы думали.

В вашей MSSMS вы можете щелкнуть правой кнопкой мыши вашу БД и выбрать Tasks -> Generate Scripts мастер для вывода всех SP, Fns и триггеров в один файл.sql.

Обязательно выберите Триггеры тоже!

Затем просто используйте Sublime или Notepad для поиска нужной вам строки. Я знаю, что это может быть довольно неэффективным и параноидальным подходом, но это работает:)

21

Источник

user1438675 24 янв '17 в 19:36

Вы также можете использовать это:

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION like '%Search_String%'

13

Источник

user2846553 04 окт '13 в 12:28

      SELECT name , type_desc , create_date , modify_date
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%High%'

8

Источник

user2416125 01 июн '21 в 20:02

select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

8

Источник

user1001101 22 май '13 в 07:42

Это может помочь вам!

SELECT DISTINCT 
      A.NAME AS OBJECT_NAME,
      A.TYPE_DESC
      FROM SYS.SQL_MODULES M 
      INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID
      WHERE M.DEFINITION LIKE '%['+@SEARCH_TEXT+']%'
      ORDER BY TYPE_DESC

8

Источник

user3929290 11 июн '15 в 06:30

 SELECT DISTINCT OBJECT_NAME([id])--,syscomments.* this gives name of 
 --stored procedures that text found

 FROM syscomments   

 WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype IN 

 ('TF','FN','V','P') AND status >= 0) AND  

 ([text] LIKE '%text to be search%' )

OBJECT_NAME([id]) -> Имя объекта (Просмотр, Процедура сохранения, Скалярная функция, Имя табличной функции)

id (int) = идентификационный номер объекта

xtype char(2) Тип объекта. Может быть одним из следующих типов объектов:

FN = скалярная функция

P = хранимая процедура

V = Просмотр

TF = табличная функция

6

Источник

user4072139 29 июн '17 в 11:12

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 '%[String]%';

6

Источник

user2264586 24 мар '15 в 08:31

Также вы можете использовать:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%flags.%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

Это включает в себя комментарии

6

Источник

user2212444 26 мар '13 в 16:25

Я создал процедуру для поиска процедур, таблиц, представлений или заданий. Первый параметр @search - это критерий поиска, @target - это цель поиска, т. Е. Процедуры, таблицы и т. Д. Если не указано, искать все. @db - указать базу данных для поиска, если она не указана, текущую базу данных. Вот запрос в динамическом sql.

ALTER PROCEDURE [dbo].[usp_find_objects]
(
    @search VARCHAR(128),
    @target VARCHAR(128) = NULL,
    @db VARCHAR(20) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(30)

IF @db <> '' SET @USEDB = 'USE ' + @db
ELSE SET @USEDB = ''

IF @target IS NULL SET @target = ''

SET @TSQL = @USEDB + '

DECLARE @search VARCHAR(128) 
DECLARE @target VARCHAR(128)

SET @search = ''%' + @search + '%''
SET @target = ''' + @target + '''

IF @target LIKE ''%Procedure%'' BEGIN
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''P''
    GROUP BY o.name
    ORDER BY o.name
END

ELSE IF @target LIKE ''%View%'' BEGIN
    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name
END

/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
    SELECT t.name AS ''TableName''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY TableName
END

ELSE IF @target LIKE ''%Job%'' BEGIN
    SELECT  j.job_id,
        s.srvname,
        j.name,
        js.step_id,
        js.command,
        j.enabled 
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END

ELSE BEGIN 
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''P''
    GROUP BY o.name
    ORDER BY o.name

    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name

    SELECT t.name AS ''Tables''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY Tables

    SELECT  j.name AS ''Jobs''
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END
'

EXECUTE sp_executesql @TSQL

5

Источник

user4271117 14 авг '17 в 19:07

Используя CHARINDEX:

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 CHARINDEX('[ABD]',m.definition) >0 ;

Использование PATINDEX:

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 PATINDEX('[[]ABD]',m.definition) >0 ;

Используя этот двойной [[]ABD] похож на побег:

WHERE m.definition LIKE '%[[]ABD]%'

4

Источник

user1389394 05 фев '13 в 09:34

Другая версия, чтобы сделать запрос более подходящим для различных практик кодирования.

      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 UPPER(M.DEFINITION) LIKE UPPER('%Your Text%');

4

Источник

user655062 12 мар '21 в 11:38

Также попробуйте это:

   SELECT ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION like '%\[ABD\]%'

4

Источник

user4488993 04 ноя '17 в 11:27

Этот запрос является поиском текста в хранимой процедуре из всех баз данных.

DECLARE @T_Find_Text VARCHAR(1000) = 'Foo'

IF OBJECT_ID('tempdb..#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAME
IF OBJECT_ID('tempdb..#T_PROCEDURE') IS NOT NULL DROP TABLE #T_PROCEDURE

CREATE TABLE #T_DBNAME
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
)

CREATE TABLE #T_PROCEDURE
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
    , Procedure_Name VARCHAR(MAX)
    , Procedure_Description VARCHAR(MAX)
)

INSERT INTO #T_DBNAME (DBName)
SELECT name FROM master.dbo.sysdatabases

DECLARE @T_C_IDX INT = 0
DECLARE @T_C_DBName VARCHAR(255)
DECLARE @T_SQL NVARCHAR(MAX)
DECLARE @T_SQL_PARAM NVARCHAR(MAX) 

SET @T_SQL_PARAM =   
    '   @T_C_DBName VARCHAR(255)
        , @T_Find_Text VARCHAR(255)
    '  


WHILE EXISTS(SELECT TOP 1 IDX FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC)
BEGIN

    SELECT TOP 1 
    @T_C_DBName = DBName 
    FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC

    SET @T_SQL = ''

    SET @T_SQL = @T_SQL + 'INSERT INTO #T_PROCEDURE(DBName, Procedure_Name, Procedure_Description)'
    SET @T_SQL = @T_SQL + 'SELECT SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION '
    SET @T_SQL = @T_SQL + 'FROM ' + @T_C_DBName +  '.INFORMATION_SCHEMA.ROUTINES  '
    SET @T_SQL = @T_SQL + 'WHERE ROUTINE_DEFINITION LIKE ''%''+ @T_Find_Text + ''%'' '
    SET @T_SQL = @T_SQL + 'AND ROUTINE_TYPE = ''PROCEDURE'' '

    BEGIN TRY
        EXEC SP_EXECUTESQL  @T_SQL, @T_SQL_PARAM, @T_C_DBName, @T_Find_Text
    END TRY
    BEGIN CATCH
        SELECT @T_C_DBName + ' ERROR'
    END CATCH

    SET @T_C_IDX = @T_C_IDX + 1
END

SELECT IDX, DBName, Procedure_Name FROM #T_PROCEDURE ORDER BY DBName ASC

2

Источник

user2194200 13 окт '17 в 02:12

Эта процедура поиска основана на /questions/10731634/poisk-teksta-v-hranimoj-protsedure-v-sql-server/10731642#10731642 и также содержит имя схемы в результатах поиска:

      CREATE PROCEDURE [dbo].[Searchinall] (@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT s.name SP_Schema_Name, OBJECT_NAME(p.OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(p.OBJECT_ID) SP_Definition
            FROM   sys.procedures p
            INNER JOIN sys.schemas s on p.schema_id = s.schema_id
            WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT s.name SP_Schema_Name, OBJECT_NAME(OBJECT_ID) View_Name
                ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
            FROM   sys.views v
            INNER JOIN sys.schemas s on v.schema_id = s.schema_id
            WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_SCHEMA, ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN

        SELECT s.name SP_Schema_Name, t.name      AS Table_Name
                ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
                INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
                INNER JOIN sys.schemas s on t.schema_id = s.schema_id
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
                Table_Name
    END
END

Вот как вы его используете:

      execute [dbo].[Searchinall] 'cust'

Вот альтернатива, которая перечисляет все объекты с определенным ключевым словом в одном запросе:

      SELECT DISTINCT
       s.name AS Schema_Name, 
       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
       INNER JOIN sys.schemas s on o.schema_id = s.schema_id
WHERE m.definition Like '%dim_forex%' ESCAPE '\' order by 3;

Этот запрос должен возвращать те же результаты, что и вызов процедуры в этом ответе.

2

Источник

user2735286 21 сен '22 в 09:35

Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1

1

Источник

user1310887 26 ноя '15 в 05:33

Время от времени я использую этот сценарий, чтобы выяснить, какие процедуры нужно изменить, или выяснить, что вообще использует столбец таблицы или эту таблицу, чтобы удалить какой-то старый мусор. Он проверяет каждую базу данных в экземпляре, на котором она запущена чудесным образом предоставленным sp_msforeachdb.

if object_id('tempdb..##nothing') is not null
    drop table ##nothing

CREATE TABLE ##nothing
(
    DatabaseName varchar(30),
    SchemaName varchar(30),
    ObjectName varchar(100),
    ObjectType varchar(50)
)

EXEC master.sys.sp_msforeachdb 
'USE ?
insert into ##nothing
SELECT 
db_name() AS [Database],
[Scehma]=schema_name(o.schema_id), 
o.Name, 
o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON o.object_id = m.object_id
WHERE 
    m.definition like ''%SOME_TEXT%'''  
--edit this text

SELECT * FROM ##nothing n
order by OBJECTname

0

Источник

user7243269 07 дек '16 в 15:37

/* 
    SEARCH SPROCS & VIEWS

    The following query will allow search within the definitions 
    of stored procedures and views.

    It spits out the results as XML, with the full definitions, 
    so you can browse them without having to script them individually.

*/

/*
   STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)
*/
DECLARE 
    @def_key varchar(128) = '%foo%',      /* <<< definition search key */
    @name_key varchar(128) = '%bar%',     /* <<< name search key       */
    @schema_key varchar(128) = 'dbo';     /* <<< schema search key     */

;WITH SearchResults AS (
    /* 
       STEP 2: DEFINE SEARCH QUERY AS CTE (Common Table Expression)
    */
    SELECT 
        [Object].object_id                       AS [object_id],    
        [Schema].name                            AS [schema_name], 
        [Object].name                            AS [object_name],
        [Object].type                            AS [object_type],
        [Object].type_desc                       AS [object_type_desc],
        [Details].definition                     AS [module_definition]
    FROM  
        /* sys.sql_modules = where the body of sprocs and views live */
        sys.sql_modules AS [Details] WITH (NOLOCK)
    JOIN
        /* sys.objects = where the metadata for every object in the database lives */
        sys.objects AS [Object] WITH (NOLOCK) ON [Details].object_id = [Object].object_id
    JOIN 
        /* sys.schemas = where the schemas in the datatabase live */
        sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_id
    WHERE 
        (@def_key IS NULL OR [Details].definition LIKE @def_key)      /* <<< searches definition */
        AND (@name_key IS NULL OR [Object].name LIKE @name_key)       /* <<< searches name       */
        AND (@schema_key IS NULL OR [Schema].name LIKE @schema_key)   /* <<< searches schema     */
)
/* 
   STEP 3: SELECT FROM CTE INTO XML
*/

/* 
    This outer select wraps the inner queries in to the <sql_object> root element 
*/
SELECT 
(
    /* 
        This inner query maps stored procedure rows to <procedure> elements
    */
    SELECT TOP 100 PERCENT
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'P'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('procedure'), TYPE
) AS [procedures],  /* <<< as part of the outer query, 
                           this alias causes the <procedure> elements
                           to be wrapped within the <procedures> element */
(
    /* 
        This inner query maps view rows to <view> elements
    */
    SELECT TOP 100 PERCENT 
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'V'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('view'), TYPE
) AS [views]  /* <<< as part of the outer query, 
                     this alias causes the <view> elements
                     to be wrapped within the <views> element */
FOR XML 
    PATH ('sql_objects')

0

Источник

user526729 02 июл '15 в 16:32

-- Applicable for SQL 2005+
USE YOUR_DATABASE_NAME //;
    GO

SELECT [Scehma] = schema_name(o.schema_id)
    ,o.NAME
    ,o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.DEFINITION LIKE '%YOUR SEARCH KEYWORDS%'
GO

0

Источник

user3960401 23 июл '17 в 14:06

Вы также можете использовать

CREATE PROCEDURE [Search](
    @Filter nvarchar(max)
)
AS
BEGIN

SELECT name
FROM   procedures
WHERE   definition LIKE '%'+@Filter+'%'

END

а потом беги

exec [Search] 'text'

-1

Источник

user3237208 25 сен '14 в 13:35

Другие вопросы по тегам sql sql-server sql-server-2008 stored-procedures system-stored-procedures

user722783 05 фев '13 в 09:33 2013-02-05 09:33 · Accepted Answer · 2013-02-05 09:33

Избегайте квадратных скобок:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Тогда квадратные скобки будут рассматриваться как строковые литералы, а не как символы подстановки.

701

Источник

user722783 05 фев '13 в 09:33