trouble with exec @sql

  • polkadot

    SSChampion

    Points: 13659

    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 1 month, 1 week ago by  polkadot.

    --Quote me

  • Sergiy

    SSC Guru

    Points: 109841

    Use the correct syntax:

    exec (@sql)

     

  • polkadot

    SSChampion

    Points: 13659

    Thanks Sergiy,  problem solved.

    --Quote me

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply