我需要搜索一个SQL server 2008的存储过程包含哪里可能 数据库字段或变量名的名称。
当前回答
select * from sys.system_objects
where name like '%cdc%'
其他回答
如何找到一个包含文本或字符串的存储过程
很多时候,我们需要在存储过程中查找文本或字符串。下面是查找包含文本的查询。
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
首先确保在用户凭证下运行查询,并且在正确的数据库上下文中运行查询。
USE YOUR_DATABASE_NAME;
否则,系统。过程不会返回任何东西。现在运行查询如下所示:
select * from sys.procedures p
join sys.syscomments s on p.object_id = s.id
where text like '%YOUR_TEXT%';
另一种选择是使用INFORMATION_SCHEMA.ROUTINES。ROUTINE_DEFINITION,但是请注意它只保存例程的有限数量的字符(即,前4000个字符)。
select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';
我在Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)上进行测试
我使用这个脚本。如果您将XML Comments更改为显示为黄色背景上的黑色文本,则可以在结果的XML列中突出显示您正在寻找的文本。(工具->选项->环境->字体和颜色[显示项:XML注释]
---------------------------------------------
-------------- Start FINDTEXT ----------
---------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = 'the text you''re looking for'
DECLARE @OverrideSearchStringWith VARCHAR(MAX)
--#############################################################################
-- Use Escape chars in Brackets [] like [%] to find percent char.
--#############################################################################
DECLARE @ReturnLen INT
SET @ReturnLen = 50;
with lastrun
as (select DEPS.OBJECT_ID
,MAX(last_execution_time) as LastRun
from sys.dm_exec_procedure_stats DEPS
group by deps.object_id
)
SELECT OL.Type
,OBJECT_NAME(OL.Obj_ID) AS 'Name'
,LTRIM(RTRIM(REPLACE(SUBSTRING(REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), ''), CHARINDEX(@SearchString, OBJECT_DEFINITION(OL.Obj_ID)) - @ReturnLen, @ReturnLen * 2), @SearchString, ' ***-->>' + @SearchString + '<<--*** '))) AS SourceLine
,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')), '&', '(A M P)'), '<', '(L T)'), '>', '(G T)'), @SearchString, '<!-->' + @SearchString + '<-->') AS XML) AS 'Hilight Search'
,(SELECT [processing-instruction(A)] = REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')
FOR
XML PATH('')
,TYPE
) AS 'code'
,Modded AS Modified
,LastRun as LastRun
FROM (SELECT CASE P.type
WHEN 'P' THEN 'Proc'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trig'
ELSE 'Func'
END AS 'Type'
,P.OBJECT_ID AS OBJ_id
,P.modify_Date AS modded
,LastRun.LastRun
FROM sys.Objects P WITH (NOLOCK)
LEFT join lastrun on P.object_id = lastrun.object_id
WHERE OBJECT_DEFINITION(p.OBJECT_ID) LIKE '%' + @SearchString + '%'
AND type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
-- AND lastrun.LastRun IS NOT null
) OL
OPTION (FAST 10)
---------------------------------------------
---------------- END -----------------
---------------------------------------------
---------------------------------------------
create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'`enter code here`
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%' + @Text + '%' and
o.xtype = case when @Type IS NULL then o.xtype else @Type end
ORDER BY Location, ObjectName
END
您也可以尝试ApexSQL Search -试用版SSMS插件从ApexSQL。
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列,添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?