How execute sql within a cursor

Question

I can't get the results of this cursor to execute

DECLARE @myVar varchar(MAX)
DECLARE @alenzi varchar(MAX)
DECLARE myCursor CURSOR FOR
SELECT name from master.sys.databases

OPEN myCursor
FETCH NEXT FROM myCursor INTO @myVar

WHILE @@Fetch _STATUS = 0
BEGIN
SET @alenzi = 'SELECT name FROM '+@myVar+'.sys.assemblies'
EXEC sp_executesql @alenzi
FETCH NEXT FROM myCursor INTO @myVar
END

CLOSE myCursor
DEALLOCATE myCursor

Answer 1

DECLARE @alenzi varchar(MAX)

Specify nvarchar(MAX) for the sp_executesql parameter type:

DECLARE @sql nvarchar(MAX);  

Answer 2

In your case I prefer not to use a cursor and just create a string with all results

DECLARE @sql nvarchar(MAX)  
  
SET @sql = (  
SELECT   
	STRING_AGG(CONCAT('SELECT ''',name,''' as [DBName], [name] as [AssemblyName] FROM [',name,'].sys.assemblies') , CHAR(10)+'UNION ALL'+CHAR(10)) WITHIN GROUP (ORDER BY [name])  
from master.sys.databases  
);  
  
EXEC sp_executesql @sql  

Answer 3

Try changing this line to
SET @alenzi = 'SELECT name FROM '+QUOTENAME(@myVar) +'.sys.assemblies' ;

also you may probably want to exclude system databases ?

Are you getting an error with your code?