how to consult database informed by variable?

Question

Just an example of query sql server:

SELECT A.name FROM [banco1].sys.procedures as A

What I need is for this query above to be changed to something like:

SELECT A.name FROM [@nomeBanco].sys.procedures as A

'Cause then I can do something like:

declare @nomeBanco varchar(50) = "Banco1"
select A.name from [@nomeBanco].sys.procedures as A

Thank you to everyone who can share ideas. Happy New Year 2021.

Answer 1

I think in this case you will have to use a dynamic query:

declare @SQL nvarchar(max);
set @SQL = N'select A.name from [' + @nomeBanco + '].sys.procedures as A';
execute sp_executesql @SQL;

I hope it helps