trouble with exec @sql

• polkadot

  SSChampion

  Points: 13664

  More actions

  August 14, 2020 at 12:32 am

  Go to Answer

  #3780371

  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

  • This topic was modified 4 years, 8 months ago by  polkadot.

  --Quote me

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  August 14, 2020 at 5:51 am

  Answer

  #3780395

  Use the correct syntax:

  exec (@sql)

   

  _____________
  Code for TallyGenerator

• polkadot

  SSChampion

  Points: 13664

  More actions

  August 14, 2020 at 3:01 pm

  #3780518

  Thanks Sergiy,  problem solved.

  --Quote me