passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

Question

I am often in need to run the sp_executesql in the context of different databases

I would like to add the same parameters as the sp_executesql into the sp_ineachdb

But when I look at this: -- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

sp_executesql [ @stmt = ] statement [ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] } ]

I can't find out how to define all the parameters to add to the sp_ineachdb.

For example the code below checks for an index inside a database:

    declare @IndexName NVARCHAR(128)='si_xdes_id'
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT  

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = 'SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM ' + @dbname + '.sys.indexes I WHERE I.Name = @IndexName  ) 
                               THEN 1 ELSE 0 END '

        SET @ParamDefinition = N'@retvalOUT int OUTPUT,
                                @IndexName SYSNAME';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        IF (@retval = 0) 
            BEGIN

               --RAISERROR('The index called %s does not exist on database %s',16,1,@IndexName,@dbname)
               RETURN

            END 
   END

for me to use it in conjunction with sp_ineachdb I have to wrap the whole thing into dynamic sql:

    DECLARE @sql NVARCHAR(MAX)=

'   declare @IndexName NVARCHAR(128)=''uc_emailAddress''
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT  

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = ''SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM '' + @dbname + ''.sys.indexes I WHERE I.Name = @IndexName  ) 
                               THEN 1 ELSE 0 END ''

        SET @ParamDefinition = N''@retvalOUT int OUTPUT,
                                @IndexName SYSNAME'';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        select @dbname,@retval

        IF (@retval = 0) 
            BEGIN

               --RAISERROR(''The index called %s does not exist on database %s'',16,1,@IndexName,@dbname)
               RETURN

            END 
   END
'

if object_id('tempdb..#radhe') is not null
   drop table #radhe
create table #radhe(dbname sysname, the_index_exist_here int)
insert into #radhe
EXEC sp_ineachdb @command = @sql, @user_only = 1;

select * from #radhe

which gives me:

question:

is there any easier way to get this done?

I would like to pass to sp_ineachdb all the parameters that I currently pass to sp_executesql and just gracefully collect the output.

Is that any possible?

Answer 1

Unfortunately, sp_ineachdb was not designed with this specific use case in mind (nested dynamic SQL with parameters), and I don't know if you can do all you want to do with this script without any dynamic SQL at all. I think this approach will be a bit simpler - you'll have to call sp_ineachdb twice, but the dynamic SQL portion is much, much simpler, and you won't have to relay parameters for the procedure to in turn pass along to its own internal sp_executesql call.

DECLARE @indexname nvarchar(128) = N'uc_emailAddress',
        @cmd       nvarchar(max) = N'SELECT db = DB_NAME(),0;';

DROP TABLE IF EXISTS #radhe;    

CREATE TABLE #radhe
(
  dbname sysname, 
  indexname nvarchar(128), 
  the_index_exist_here bit
);

INSERT #radhe(dbname,the_index_exist_here)
EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

UPDATE #radhe SET indexname = @indexname;

SET @cmd = N'UPDATE r SET the_index_exist_here = 1
    FROM #radhe AS r
    INNER JOIN sys.indexes AS i
    ON r.dbname = DB_NAME()
    AND i.name = r.indexname;';

EXEC master.dbo.sp_ineachdb @cmd;

SELECT dbname, the_index_exist_here FROM #radhe;