在数据库中查找包含指定关键字的存储过程或函数

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:          <Author,,>
-- Create date: <Create Date,,>
-- Description:    <Description,,在数据库中查找包含指定关键字的存储过程或函数
-- =============================================
ALTER PROCEDURE dbo.SearchProcOrFuncProc
  @srh    NVARCHAR(MAX), -- 要查找的内容，空格分词（需要注意XML转义）
  @srh1   NVARCHAR(MAX), -- 要查找的内容1，空格分词（需要注意XML转义，先查找 @srh 再在 @srh 的搜索结果基础上查找 @srh1）
  @dbs    NVARCHAR(MAX), -- 在哪些数据库中查找，用空格分隔多个数据库名称
  @orders NVARCHAR(MAX)  -- 查询结果排序
AS
BEGIN
  SET NOCOUNT ON;

  IF ISNULL(@srh, N'') = N''
  BEGIN
    PRINT N'请输入需要查找的内容';
    RETURN;
  END;

  DECLARE @tempXML XML;

  SET @tempXML = N'<v>' + REPLACE(REPLACE(@srh, N' ', N'</v><v>'), N'&nbsp;', N' ') + N'</v>';

  DECLARE @srhTbl TABLE (srh NVARCHAR(MAX));
  INSERT @srhTbl (srh)
  SELECT t.val
  FROM   (SELECT t.c.value(N'.', N'NVARCHAR(MAX)') val
          FROM   @tempXML.nodes(N'/v') t(c) ) t
  WHERE  t.val <> N'';

  IF NOT EXISTS (SELECT 1 FROM @srhTbl)
  BEGIN
    PRINT N'请输入需要查找的内容';
    RETURN;
  END;

  DECLARE @dbTbl TABLE (dbname NVARCHAR(MAX));
  IF ISNULL(@dbs, N'') = N''
  BEGIN
    INSERT @dbTbl (dbname)
    SELECT name
    FROM   sys.sysdatabases
    WHERE  name NOT IN ('master', 'tempdb', 'model', 'msdb', 'OldCustomerBak');
  END;
  ELSE
  BEGIN
    SET @tempXML = N'<v>' + REPLACE(@dbs, N' ', N'</v><v>') + N'</v>';

    INSERT @dbTbl (dbname)
    SELECT t.val
    FROM   (SELECT t.c.value(N'.', N'NVARCHAR(MAX)') val
            FROM   @tempXML.nodes(N'/v') t(c) ) t
    WHERE  t.val <> N'';

    IF NOT EXISTS (SELECT 1 FROM @dbTbl)
    BEGIN
      PRINT N'请输入需要查找的数据库';
      RETURN;
    END;
  END;

  DECLARE @sql NVARCHAR(MAX), @condition NVARCHAR(MAX);
  SET @condition = (SELECT N'sc.text LIKE ''%' + REPLACE(t.srh, '''', '''''') + '%'' ESCAPE (''~'') OR '
                    FROM   @srhTbl t
                   FOR XML PATH(N''));
  SET @condition = LEFT(@condition, LEN(@condition) - LEN(N' OR '));

  IF ISNULL(@srh1, N'') <> N''
  BEGIN
    SET @tempXML = N'<v>' + REPLACE(@srh1, N' ', N'</v><v>') + N'</v>';

    DECLARE @srhTbl1 TABLE (srh NVARCHAR(MAX));
    INSERT @srhTbl1 (srh)
    SELECT t.val
    FROM   (SELECT t.c.value(N'.', N'NVARCHAR(MAX)') val
            FROM   @tempXML.nodes(N'/v') t(c) ) t
    WHERE  t.val <> N'';

    IF EXISTS (SELECT 1 FROM @srhTbl1)
    BEGIN
      DECLARE @condition1 NVARCHAR(MAX);
      SET @condition1 = (SELECT N'sc.text LIKE ''%' + REPLACE(t.srh, '''', '''''') + '%'' ESCAPE (''~'') OR '
                         FROM   @srhTbl1 t
                        FOR XML PATH(N''));
      SET @condition1 = LEFT(@condition1, LEN(@condition1) - LEN(N' OR '));

      SET @sql = (SELECT N'SELECT DISTINCT ''' + t.dbname
                         + N''' dbname, so.name COLLATE DATABASE_DEFAULT name, so.xtype COLLATE DATABASE_DEFAULT xtype, o.modify_date modifyDate FROM '
                         + t.dbname + N'.sys.sysobjects so INNER JOIN ' + t.dbname + N'.sys.syscomments sc ON sc.id = so.id LEFT JOIN ' + t.dbname
                         + N'.sys.objects o ON o.object_id = so.id WHERE so.id IN (SELECT DISTINCT sc.id FROM ' + t.dbname + N'.sys.syscomments sc WHERE '
                         + @condition + N') AND (' + @condition1 + N') UNION ALL '
                  FROM   @dbTbl t
                 FOR XML PATH(N''));
    END;
  END;

  IF @sql IS NULL
  BEGIN
    SET @sql = (SELECT N'SELECT DISTINCT ''' + t.dbname
                       + N''' dbname, so.name COLLATE DATABASE_DEFAULT name, so.xtype COLLATE DATABASE_DEFAULT xtype, o.modify_date modifyDate FROM '
                       + t.dbname + N'.sys.syscomments sc INNER JOIN ' + t.dbname + N'.sys.sysobjects so ON so.id = sc.id LEFT JOIN ' + t.dbname
                       + N'.sys.objects o ON o.object_id = so.id WHERE ' + @condition + N' UNION ALL '
                FROM   @dbTbl t
               FOR XML PATH(N''));
  END;
  SET @sql = LEFT(@sql, LEN(@sql) - LEN(N' UNION ALL ')) + N' ORDER BY ' + CASE WHEN ISNULL(@orders, N'') = N'' THEN N'modifyDate DESC' ELSE @orders END;

  EXEC (@sql);
END;

GO