如何在 SQLServer 过程/触发器中查找文本？

我有一个链接服务器，将改变。有些过程像这样调用链接服务器: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE。我们也有触发器做这种工作。我们需要找到所有使用 [10.10.100.50]来改变它的地方。

在 SQLServerManagementStudioExpress 中，我没有在 VisualStudio 中找到类似“ find In whole database”的特性。一个特殊的系统选择能帮助我找到我需要的吗？

362076 次浏览
小开
最佳答案

下面是我在系统中用来查找文本的过程的一部分... ..。

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

我用这个工作。在@TEXT 字段中去掉[] ，似乎想要返回所有..。

SET NOCOUNT ON


DECLARE @TEXT   VARCHAR(250)
DECLARE @SQL    VARCHAR(250)


SELECT  @TEXT='10.10.100.50'


CREATE TABLE #results (db VARCHAR(64), objectname VARCHAR(100),xtype VARCHAR(10), definition TEXT)


SELECT @TEXT as 'Search String'
DECLARE #databases CURSOR FOR SELECT NAME FROM master..sysdatabases where dbid>4
DECLARE @c_dbname varchar(64)
OPEN #databases
FETCH #databases INTO @c_dbname
WHILE @@FETCH_STATUS  -1
BEGIN
SELECT @SQL = 'INSERT INTO #results '
SELECT @SQL = @SQL + 'SELECT ''' + @c_dbname + ''' AS db, o.name,o.xtype,m.definition '
SELECT @SQL = @SQL + ' FROM '+@c_dbname+'.sys.sql_modules m '
SELECT @SQL = @SQL + ' INNER JOIN '+@c_dbname+'..sysobjects o ON m.object_id=o.id'
SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@TEXT+'%'''
EXEC(@SQL)
FETCH #databases INTO @c_dbname
END
CLOSE #databases
DEALLOCATE #databases


SELECT * FROM #results order by db, xtype, objectname
DROP TABLE #results
小开

我以前用过这个:

在这种特殊情况下，需要跨存储过程替换特定的字符串时，第一个链接可能更相关。

有点跑题的是，快速查找外接程序对于使用 SQLServerManagementStudio 搜索对象名也很有用。有一个带有一些改进的 修改版本可用，另一个 更新的版本也可用于 Codrux，还有一些其他有用的外接程序。

小开

你会发现

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

它将列出不同的存储过程名称，其中包含类似于存储过程中的“ User”这样的文本

小开

这对你有用:

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
小开 
-- 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');
小开

有比每次链接服务器更改时修改存储过程、函数和视图的文本更好的 很多解决方案。这里有一些选择:

  1. 更新链接服务器。不要使用以其 IP 地址命名的链接服务器，而是创建一个以资源名(如 Finance或 DataLinkProd等)命名的新链接服务器。然后，当您需要更改到达哪个服务器时，更新链接服务器以指向新服务器(或删除它并重新创建它)。

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

可能还有更多的选择。我强烈推荐使用优越的预处理、配置或间接技术，而不是更改人工编写的脚本。自动更新机器创建的脚本是好的，这是预处理。手动操作很糟糕。

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

[迟到的回答，但希望有用]

使用系统表并不总是给出100% 正确的结果，因为可能存在某些存储过程和/或视图被加密的可能性，在这种情况下，您需要使用 DAC连接来获得所需的数据。

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

如果对象被加密，系统表将为文本列赋空值。

小开

使用 select 语句进行的任何搜索都只产生对象名，其中 search 关键字包含。 最简单有效的方法是获取过程/函数的脚本，然后在生成的文本文件中搜索，我也遵循这个技巧:)所以你是准确的。

小开

可以使用以下 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%'
小开

我在 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
小开 
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your Text%'
小开

只是写了这个通用的完整的外部交叉参考

create table #XRefDBs(xtype varchar(2),SourceDB varchar(100), Object varchar(100), RefDB varchar(100))


declare @sourcedbname varchar(100),
@searchfordbname varchar(100),
@sql nvarchar(4000)
declare curs cursor for
select name
from sysdatabases
where dbid>4
open curs
fetch next from curs into @sourcedbname
while @@fetch_status=0
begin
print @sourcedbname
declare curs2 cursor for
select name
from sysdatabases
where dbid>4
and name <> @sourcedbname
open curs2
fetch next from curs2 into @searchfordbname
while @@fetch_status=0
begin
print @searchfordbname
set @sql =
'INSERT INTO #XRefDBs (xtype,SourceDB,Object, RefDB)
select DISTINCT o.xtype,'''+@sourcedbname+''', o.name,'''+@searchfordbname+'''
from '+@sourcedbname+'.dbo.syscomments c
join '+@sourcedbname+'.dbo.sysobjects o on c.id=o.id
where o.xtype in (''V'',''P'',''FN'',''TR'')
and (text like ''%'+@searchfordbname+'.%''
or text like ''%'+@searchfordbname+'].%'')'
print @sql
exec sp_executesql @sql
fetch next from curs2 into @searchfordbname
end
close curs2
deallocate curs2
fetch next from curs into @sourcedbname
end
close curs
deallocate curs


select * from #XRefDBs

提交答案