Executing dynamic SQL programmatically [closed]

Question

Usage (all parameters are optional):

declare @options int = ( -- SQL "enum"
    select a.ExcludeUserDatabases | a.ExcludeOfflineDatabases
    from dbo.ForEachDatabaseOptions() as a
);

execute dbo.usp_ForEachDatabase
    @Command = N'print Db_Name();'
  , @Options = @options;

• execute dbo.usp_ForEachDatabase; -- prints Db_Name() in every database by default
• execute dbo.usp_ExecuteSqlCommand; -- prints Db_Name() in procedure's database by default

You can also reference the code in dbo.usp_ForEachDatabase or dbo.usp_ExecuteSqlCommand for an example on how to call dbo.usp_ExecuteSqlCommands.

create type dbo.ListOfSqlCommands as table (
    SequenceNumber int  
  , Command nvarchar(max)
  , DatabaseName sysname
);
go    

create function dbo.IsNullOrEmpty (
    @x nvarchar(max) = null
)
returns table
with schemabinding as
return (
    select case IsNull(@x, '') when '' then 1 else 0 end as b
);
go

create procedure dbo.usp_ExecuteSqlCommands (
    @CommandList dbo.ListOfSqlCommands readonly
  , @ThrowOnError bit = 1  
  , @Debug bit = 1
)
with execute as caller
as
begin;
    set nocount on;

    begin try;
        declare @debugCommands dbo.ListOfSqlCommands;              
        declare @databaseName sysname;
        declare @outerStatement nvarchar(max);
        declare @innerStatement nvarchar(max);
        declare @index int = 0; -- loop index
        declare @length int = ( -- loop length
            select Count(1)
            from @CommandList as a
            cross apply dbo.IsNullOrEmpty(a.Command) CommandIsNullOrEmpty
            where CommandIsNullOrEmpty.b = 0
        );

        while(@index < @length) -- loop through each command in list
        begin;
            select @innerStatement = a.Command
                 , @databaseName = a.DatabaseName
            from @CommandList as a
            cross apply dbo.IsNullOrEmpty(a.Command) CommandIsNullOrEmpty
            where CommandIsNullOrEmpty.b = 0
            order by a.SequenceNumber asc
            offset @index rows
            fetch next 1 rows only;

            if (@Debug = 0) -- execute sql command in target database
            begin;
                set @outerStatement = QuoteName(@databaseName, '[') + N'.sys.sp_executesql @stmt = @SqlStatement';

                execute sys.sp_executesql
                    @stmt = @outerStatement
                  , @params = N'@SqlStatement nvarchar(max)'
                  , @SqlStatement = @innerStatement;                
            end;
            else -- store command for debugging
            begin;
                insert into @debugCommands (SequenceNumber, Command, DatabaseName)
                select @index as SequenceNumber
                     , @innerStatement as Command
                     , @databaseName as DatabaseName;
            end;

            set @index = @index + 1; -- increment loop index
        end;

        if (@Debug != 0) -- print commands
        begin;
            select * from @debugCommands;
        end;
    end try
    begin catch
        if (@ThrowOnError = 1)
        begin;
            throw;
        end;
    end catch;

    set nocount off;
end;
go

create procedure dbo.usp_ExecuteSqlCommand (
    @Command nvarchar(max) = N'select Db_Name() as DatabaseName'
  , @DatabaseName sysname = null
  , @ThrowOnError bit = 1
  , @Debug bit = 0
)
with execute as caller
as
begin;
    declare @commandList dbo.ListOfSqlCommands;

    insert into @commandList
    select 0 as SequenceNumber
         , @Command as Command
         , Coalesce(@DatabaseName, Db_Name()) as DatabaseName;

    execute dbo.usp_ExecuteSqlCommands
        @CommandList = @commandList
      , @ThrowOnError = @ThrowOnError
      , @Debug = @Debug;
end;
go

create function dbo.ForEachDatabaseOptions ()   
returns table
with schemabinding as
return (
    select 0 as [None]
         , 1 as ExcludeSystemDatabases
         , 2 as ExcludeUserDatabases
         , 4 as ExcludeOfflineDatabases
);
go

create procedure dbo.usp_ForEachDatabase (
    @Command nvarchar(max) = N'print ''Hello '' + Db_Name() + ''!'';'
  , @Options int = 5 -- set the ExcludeSystemDatabases (1) and ExcludeOfflineDatabases (4) flags by default
  , @ThrowOnError bit = 1
  , @Debug bit = 0
)
with execute as caller
as
begin;
    set nocount on;

    declare @commandList dbo.ListOfSqlCommands;

    insert into @commandList
    select 0 as SequenceNumber
         , Replace(@Command, N'?', a.name) as Command -- TODO: Make this a bit more robust...
         , a.name as DatabaseName
    from sys.databases as a
    cross apply dbo.ForEachDatabaseOptions()
    outer apply (
        select ForEachDatabaseOptions.ExcludeSystemDatabases
        from (values(1), (2), (3), (4)) as SytemDatabaseIds (n)
        where a.database_id = SytemDatabaseIds.n
    ) as SystemDatabases (Flag)
    outer apply (
        select ForEachDatabaseOptions.ExcludeOfflineDatabases
        where a.[state] = 6
    ) as OfflineDatabases (Flag)
    where Coalesce(SystemDatabases.Flag, 0) & @Options != 1 -- excludes system databases if @Options has the ExcludeSystemDatabases flag set
      and Coalesce(SystemDatabases.Flag, 2) & @Options != 2 -- excludes user databases if @Options has the ExcludeUserDatabases flag set
      and Coalesce(OfflineDatabases.Flag, 0) & @Options != 4 -- excludes offline databases if @Options has the ExcludeOfflineDatabases flag set
    order by a.name asc;

    execute dbo.usp_ExecuteSqlCommands
        @CommandList = @commandList
      , @ThrowOnError = @ThrowOnError
      , @Debug = @Debug;

    set nocount off;
end;