如何在SQL Server存储过程/触发器中查找文本？

Question

如何在SQL Server存储过程/触发器中查找文本？

sql-serverstored-procedurestriggerslinked-server

192

我有一个将发生更改的链接服务器。有些存储过程像这样调用链接服务器：[10.10.100.50].dbo.SPROCEDURE_EXAMPLE。我们还有触发器也进行这种工作。我们需要找到所有使用[10.10.100.50]的地方来进行更改。

在SQL Server Management Studio Express中，我没有找到类似于Visual Studio中的“查找整个数据库”的功能。是否有一种特殊的sys-select可以帮助我找到所需内容？

- Victor Rodrigues

14个回答

23

你可以像这样找到它

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'

它将列出包含文本"User"的存储过程名称。更多信息

- ashish.chotalia

10

请注意，syscomments表以8000个字符的块存储值，所以如果你不幸地遇到要搜索的文本刚好分割在其中一条记录的边界上，那么使用这种方法就无法找到它。 - ErikE

18

[回答可能晚了但希望有用]

使用系统表并不总是能够给出100%的正确结果，因为有可能一些存储过程和/或视图被加密了，这时你需要使用DAC连接获取所需数据。

我建议使用第三方工具，比如ApexSQL Search，可以轻松处理加密对象。

Syscomments系统表会对文本列给出空值，如果对象被加密。

- Dwoolk

13

-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = 'employee';

-- Get the schema name, table name, and table type for:

-- Table names
SELECT
       TABLE_SCHEMA  AS 'Object Schema'
      ,TABLE_NAME    AS 'Object Name'
      ,TABLE_TYPE    AS 'Object Type'
      ,'Table Name'  AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
 --Column names
SELECT
      TABLE_SCHEMA   AS 'Object Schema'
      ,COLUMN_NAME   AS 'Object Name'
      ,'COLUMN'      AS 'Object Type'
      ,'Column Name' AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
      SPECIFIC_SCHEMA     AS 'Object Schema'
      ,ROUTINE_NAME       AS 'Object Name'
      ,ROUTINE_TYPE       AS 'Object Type'
      ,ROUTINE_DEFINITION AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
      AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');

- Heba Mahmoud

1

这不包括像所问的触发器。 - Enkode

适用于_views、存储过程、用户定义表格吗？还适用于触发器、函数、约束、规则和默认值吗？ - Kiquenet

5

这将对您有所帮助：

use [ANALYTICS]  ---> put your DB name here
GO
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, 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 '%SEARCH_WORD_HERE%' collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO

- laurens

不适用于约束条件 (type = 'C') ? - Kiquenet

4

有比每次更改链接服务器时修改存储过程、函数和视图的文本更好的解决方案。以下是其中一些选项：

1. 更新链接服务器。不要使用以其IP地址命名的链接服务器，而是创建一个新的链接服务器，名称为例如“Finance”或“DataLinkProd”等资源的名称。那么当您需要更改要访问的服务器时，请更新链接服务器以指向新服务器（或删除它并重新创建它）。

2. 您不能为链接服务器或模式创建同义词，但可以为位于链接服务器上的对象创建同义词。例如，您的过程[10.10.100.50].dbo.SPROCEDURE_EXAMPLE可以被别名化。也许创建一个模式datalinkprod，然后创建SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;。然后，编写一个接受链接服务器名称的存储过程，查询来自远程数据库的所有潜在对象并（重新）为它们创建同义词。所有SP和函数只需重写一次以使用以datalinkprod开头的同义词名称，此后要从一个链接服务器更改到另一个链接服务器，只需执行EXEC dbo.SwitchLinkedServer '[10.10.100.51]'；几秒钟内，您将使用不同的链接服务器。

可能还有更多选项。我强烈建议使用预处理、配置或间接的卓越技术，而非更改人为编写的脚本。自动更新机器生成的脚本是可以接受的，这是预处理。手动操作很糟糕。

- ErikE

我同意你的建议。但在像OP描述的情况下，你仍然需要找到所有包含服务器IP的存储过程。即使你只需要做一次，手动完成可能会是非常繁琐的工作。 - Paul Groke

@PaulGroke 是的，这个“很多”的工作是由于根深蒂固的系统中糟糕的技术选择所导致的技术债务。从这种情况中恢复需要时间——偿还已经积累的债务。但我的建议是如何建立技术财富——现在花更多的时间，以便将来更快、更敏捷、更可靠。阅读文章Big Ball of Mud，了解一些相关的想法。 - ErikE

我的意思是：使用其他人在这里发布的SELECT语句之一来减少“偿还债务”工作有什么问题？ - Paul Groke

@PaulGroke，快速查找可能引用链接服务器的对象没有任何问题。但是你知道那句老话，“授人以鱼不如授人以渔”吧？是的，就是那个。 - ErikE

@ErikE 问题在于 - 你并没有教他如何钓鱼，只是说如果他去钓鱼，就能得到食物。你的回答是很好的建议，但并没有帮助OP实际实现它。添加一种查找这些引用的方法，以便您可以用更好的设计替换它们，将使这个答案变得更好。 - T. Sar

@T.Sar感谢您的建设性评论。有时候，知道某件事情是可能的，已经是成功的一半了。 - ErikE

2

select text
from syscomments
where text like '%your text here%'

- Rez.Net

2

我在SQL2008中尝试了这个功能，它可以一次搜索所有数据库。

Create table #temp1 
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))

Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)

set  @searhString='firstweek'

declare db_cursor cursor for 
select dbid, [name] 
from master..sysdatabases
where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')



open db_cursor
fetch next from db_cursor into @dbid, @dbname

while (@@fetch_status = 0)
begin
    PRINT 'DB='+@dbname
    set @longstr = 'Use ' + @dbname + char(13) +        
        'insert into #temp1 ' + char(13) +  
        'SELECT @@ServerName,  ''' + @dbname + ''', Name 
        , case  when [Type]= ''P'' Then ''Procedure''
                when[Type]= ''V'' Then ''View''
                when [Type]=  ''TF'' Then ''Table-Valued Function'' 
                when [Type]=  ''FN'' Then ''Function'' 
                when [Type]=  ''TR'' Then ''Trigger'' 
                else [Type]/*''Others''*/
                end 
        , '''+ @searhString +''' FROM  [SYS].[SYSCOMMEnTS]
        JOIN  [SYS].objects ON ID = object_id
        WHERE TEXT LIKE ''%' + @searhString + '%'''

 exec (@longstr)
 fetch next from db_cursor into @dbid, @dbname
end

close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1

- yenfang chang

1

您可以使用以下SQL在所有数据库对象的定义中进行搜索：

SELECT 
    o.name, 
    o.id, 
    c.text,
    o.type
FROM 
    sysobjects o 
RIGHT JOIN syscomments c 
    ON o.id = c.id 
WHERE 
    c.text like '%text_to_find%'

- Joaquinglezsantos

0

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Your Text%'

- sansalk

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- mike · Accepted Answer

这是我在系统上使用的一部分程序，用于查找文本....

DECLARE @Search varchar(255)
SET @Search='[10.10.100.50]'

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 '%'+@Search+'%'
    ORDER BY 2,1