How to properly form an array loop to use in an SQL statement

I am trying to use an array to pull table information. My array build seems to be fine and it is created with the correct number of rows.

THis is what I have so far:

DECLARE @myTableVariable TABLE (name varchar(30))

insert into @myTableVariable SELECT [NAME] FROM SYS.DATABASES

SELECT
   TABLE_CATALOG AS 'DATABASE',
   TABLE_NAME,
   COLUMN_NAME,
   DATA_TYPE,
   CHARACTER_MAXIMUM_LENGTH
FROM @myTableVariable.information_schema.columns

I hope my question makes sense! Thanks in advance!

I know there needs to be some sort of WHILE loop around the SQL statement portion, but I am unsure how to properly form it. I have been looking around with no luck.

You can achieve what you're trying to do with a little bit of Dynamic SQL.

In SQL Server I would prefer to use the system DMVs instead of Information_Schema (which really only exist for compatability) however, assuming you have permission to access the database(s) and don't have conflicting collations, the following should help:

declare @Sql nvarchar(max);
with db as (select name from master.sys.databases where database_id > 4)
select @Sql = string_agg(Convert(nvarchar(max), Concat(N'
  select 
  TABLE_CATALOG as [DATABASE],
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH
  from ', QuoteName(db.name), N'.information_schema.columns')), N' union all ')
from db;
select @Sql;
exec (@Sql);