My code is below, I am getting the following error message, but I'm not sure why:
Msg 102, Level 15, State 1, Line 37 Incorrect syntax near '
Does anyone have any suggestions?
use [NA];
go
-----------------------------
exec sp_MSforeachDB
'
use ?;
select top 1
db_name() as [database_name],
s.[name] as [schema_name],
b.[name] as [object_name],
b.[type] as [object_type],
case
when b.[type] = ''P'' then ''SQL Stored Procedure''
when b.[type] = ''RF'' then ''Replication-filter-procedure''
when b.[type] = ''V'' then ''View''
when b.[type] = ''TR'' then ''SQL DML Trigger''
when b.[type] = ''FN'' then ''SQL Scalar Function''
when b.[type] = ''IF'' then ''SQL inline table-valued function''
when b.[type] = ''TF'' then ''SQL Table-Valued Function''
when b.[type] = ''R'' then ''Rule (old-style, stand-alone)''
else ''
end as [object_type_description],
--------------------
a.[object_id],
object_definition(a.[object_id]) as [object_definition_results]
--------------------
from
[sys].[sql_modules] as a
inner join
[sys].[objects] as b
on
a.[object_id] = b.[object_id]
inner join
[sys].[schemas] as s
on
b.[schema_id] = s.[schema_id]
where
object_definition(a.[object_id]) like ''%SchlGrpTestSchlName%''
'
Testing your query by building into a variable it was clear the error is
else ''
this should be
else ''''
otherwise it's not properly embedding the single quotes into the string.