我需要搜索一个SQL server 2008的存储过程包含哪里可能 数据库字段或变量名的名称。


当前回答

自己抓一个免费的红门SQL搜索工具,开始享受在SQL Server搜索!:-)

这是一个非常有用的工具,是的!它是完全,绝对免费的任何用途。

其他回答

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FieldName%' 
AND ROUTINE_TYPE='PROCEDURE'

对于任何更新于SQL server 2000的SQL server:

SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition  
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
WHERE sm.definition like '%searchString%'  
ORDER BY  o.type, o.name, o.object_id

如果有人被SQL server 2000卡住,表sql_modules不存在,所以你会使用syscomments,你会得到多个存储过程的记录大于4000个字符,但它们会有相同的c.number字段,所以你可以把这些部分组合在一起,以获得完整的存储过程文本:

    Select o.id, c.number, o.name, c.text  
    from syscomments c 
    inner join sysobjects o on o.id = c.id 
    where c.encrypted = 0 and o.type = 'P'  
      and c.id in  
     (Select id from syscomments where text like '%searchtext%')
    order by objecttype, o.name, o.id, c.number, c.colid        
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

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

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

如何找到一个包含文本或字符串的存储过程

很多时候,我们需要在存储过程中查找文本或字符串。下面是查找包含文本的查询。

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

欲了解更多信息,请查看下面给出的URL。

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string