I want to search all databases having sprocs containing text 'text I am looking for' for example. Print @sql gives me a working query but at run time get error. What tweak needed to get this exec sql to run?
Error
Msg 203, Level 16, State 2, Line 33
The name '
SELECT DISTINCT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc
FROM master.sys.sql_modules m
INNER JOIN master.sys.objects o ON m.object_id = o.object_id
INNER JOIN master.sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition Like '%text I am looking for%'' is not a valid identifier.
Query
USE [CSM_DataFeeds_IS]
GO
DECLARE @cmd as varchar(max),
@db_name as varchar(100),
@text as varchar(100)
--get list of scfeed tables
DECLARE db_name CURSOR LOCAL FOR
SELECT name
FROM sys.databases
OPEN db_name
FETCH NEXT FROM db_name INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @text = N'%text I am looking for%'
declare @sql nvarchar(max)
print @db_name
set @sql = N'
SELECT DISTINCT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc
FROM ' + @db_name + '.sys.sql_modules m
INNER JOIN ' + @db_name + '.sys.objects o ON m.object_id = o.object_id
INNER JOIN ' + @db_name + '.sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition Like ''' + @text +''''
print @sql
exec @Sql
FETCH NEXT FROM db_name INTO @db_name
END
CLOSE db_name
DEALLOCATE db_name
--Quote me
August 14, 2020 at 3:01 pm
Thanks Sergiy, problem solved.
--Quote me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply