Using local variables to search for procedure names in sys.procedures (SQL)

Question

I want to run a series of queries (same query really) on a few databases in a given server where I only change the table name being looked up.

USE database 
Go

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

there are about 5 servers, with 10 databases on average to search. I don't want to copy and past the string each time. I want to declare a @string and update it once.

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

I've tried it two ways and I can't get it to work. This is what I've tried:

option 1:

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

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

option 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

Neither seem to work. I've found a few tickets posted about how to declare @variable, and use it part of the USE statement to change the database name but that I don't need what I need is to modify a procedure name and search it across each database without a series of outputs.

I get the following error messages:

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

There was a comment provided below that suggested i print the data because im missing single quotes, thank you that did fix a part of the error however this is the code now and it still doesn't work:

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

Leveraging sys.databases you can use dynamic sql here to avoid the dreaded loop. There are additional comments in the code to help provide contextual understanding.

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