使用局部变量在sys.procedures (SQL)中搜索过程名称

Question

我想在给定服务器中的几个数据库上运行一系列查询(实际上是相同的查询)，在这些数据库中，我只更改正在查找的表名。

USE database 
Go

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%value%'

大约有5台服务器，平均有10个数据库可供搜索。我不想每次都复制和通过字符串。我想声明一个@字符串并更新一次。

DECLARE @string_lookup NVARCHAR(MAX) = '%stringtolookup%'

我试过两种方法，但我无法让它起作用。这就是我尝试过的：

备选案文1：

DECLARE @string_lookup NVARCHAR(MAX) = '%stringtolookup%'

Exec ('SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE ' + @string_lookup)

备选案文2：

DECLARE @var VARCHAR(100) ,@SQL NVARCHAR(max) = ''

SET @var = '%stringtolookup%'
SET @sql = 'select name From sys.procedures WHERE  Object_definition(object_id) LIKE ' + @var

EXEC SP_EXECUTESQL @SQL

两者似乎都不起作用。我发现了一些关于如何声明@变量，并使用它作为use语句的一部分来更改数据库名称的票证，但我不需要修改一个过程名，并在每个数据库中搜索它，而不需要一系列输出。

我收到以下错误消息：

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@string_lookup".

下面提供了一条注释，建议我打印数据，因为我缺少单引号，谢谢您修复了错误的一部分，但是这是现在的代码，它仍然不能工作：

DECLARE @string_lookup VARCHAR(100) = '%web_app%'
DECLARE @SQL NVARCHAR(max) = ''
SET @sql = 'select name From sys.procedures WHERE  Object_definition(object_id) LIKE ''' + @var + ''''

USE db1
Go
    EXEC SP_EXECUTESQL @SQL

USE db2
Go
    EXEC SP_EXECUTESQL @SQL

USE db3
Go
    EXEC SP_EXECUTESQL @SQL

Answer 1

利用sys.databases，您可以在这里使用动态sql来避免可怕的循环。代码中还有更多的注释，以帮助提供上下文理解。

DECLARE @string_lookup VARCHAR(100) = '%web_app%'
DECLARE @SQL NVARCHAR(max) = ''

select @SQL = @SQL + 'select ''' + quotename(db.name) + ''' as DatabaseName, name COLLATE SQL_Latin1_General_CP1_CI_AS from ' + quotename(db.name) + '.sys.procedures where Object_definition(object_id) LIKE ''' + @string_lookup + ''' UNION ALL '
from sys.databases db
where db.state_desc = 'ONLINE' --exclude OFFLINE databases
--you could include any other predicates here to eliminate any other databases


select @SQL = left(@SQL, LEN(@SQL) - 10)

select @SQL --uncomment the next line when you are comfortable the dynamic sql is correct
--exec sp_executesql @SQL

Answer 2

如果首先打印出SQL，则可以看到错误所在。您缺少字符串周围的单引号。

DECLARE @var VARCHAR(100) ,@SQL NVARCHAR(max) = ''

SET @var = '%stringtolookup%'
SET @sql = 'select name From sys.procedures WHERE  Object_definition(object_id) LIKE ''' + @var + ''''
print @sql

EXEC SP_EXECUTESQL @SQL

Answer 3

可以在声明变量的行上设置变量，因此不需要动态SQL。

declare @var varchar(100) = '%stringtolookup%'

select name
From sys.procedures 
WHERE  object_definition(object_id) LIKE @var