提问



我想从我的所有数据库stored procedures中搜索文本。我使用下面的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]%';


我想在包括方括号的所有stored procedures中搜索[ABD],但它没有给出正确的结果。如何更改我的查询来实现这一目标?

最佳参考


逃避方括号:


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


然后方括号将被视为字符串文字而不是通配符。

其它参考1


试试这个请求:


查询


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

其它参考2


您是否尝试过使用某些第三方工具进行搜索?有几个是免费的,过去我节省了很多时间。


下面是我使用的两个SSMS Addins,取得了很好的成功。


ApexSQL搜索 - 搜索数据库中的模式和数据,并具有其他功能,如依赖性跟踪等... [35]


SSMS工具包 - 具有与前一个相同的搜索功能和其他一些很酷的功能。 SQL Server 2012不是免费的,但仍然非常实惠。 [36]


我知道这个答案并不是100%与问题相关(更具体),但希望其他人会发现这个问题很有用。

其它参考3


我通常运行以下操作来实现:


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

其它参考4


Redgate的SQL搜索是一个很好的工具,它是SSMS的免费插件。[37]

其它参考5


使用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


现在 - 设置如下所示的快捷键,


[38]


因此,下次每当您想在Store procedureViewsFunctionsTables这四个对象中的任何一个中找到特定文本时。您只需要编写该关键字并按下快捷键即可。


例如:我想搜索PaymentTable然后在查询编辑器中写下PaymentTable并按下快捷键ctrl+4 - 它将为您提供完整的结果。

其它参考6


你也可以用这个:


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

其它参考7


请把它作为一个脏的替代方案,但这特别是当我不熟悉数据库项目时,这让我多次失败。有时你试图在所有SP中搜索一个字符串并忘记某些相关逻辑可能隐藏在函数和触发器之间,或者它可能只是措辞与你想象的不同。


在MSSMS中,您可以右键单击数据库并选择
Tasks -> Generate Scripts 向导将所有SP,Fns和触发器输出到单个.sql文件中。


[39]


一定要选择触发器!


[40]


然后只需使用Sublime或记事本来搜索您需要查找的字符串。 我知道这可能是非常低效和偏执的方法,但它有效:)

其它参考8


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

其它参考9


它可能对你有所帮助!


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

其它参考10


你也可以使用:


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


这包括评论

其它参考11


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

其它参考12


使用CHARINDEX:[41]


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:[42]


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]%'

其它参考13


 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 =表函数

其它参考14


/* 
    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')

其它参考15


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

其它参考16


每隔一段时间我就会使用这个脚本来确定要修改哪些过程,或者弄清楚是什么使用了表的列,或者根本用来删除一些旧垃圾。它通过精彩提供的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 

其它参考17


-- 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

其它参考18


此查询是来自所有数据库的存储过程中的搜索文本。


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

其它参考19


也尝试这个:


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

其它参考20


我创建了一个搜索过程,表,视图或作业的过程。第一个参数@search是搜索条件,@ target仅用于指定过程或其他参数。如果没有指定,它将搜索所有。 @db是指定要搜索的数据库。这是查询。


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

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

其它参考21


你也可以使用


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

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

END


然后跑


exec [Search] 'text'