USE master;

IF OBJECT_ID('tempdb..#dbs')IS NOT NULL BEGIN;
	DROP TABLE #dbs;
END;

SELECT [db].[name]
INTO #dbs
  FROM sys.databases db
 WHERE [db].[name] NOT IN ( 'master', 'msdb', 'tempdb', 'distribution', 'model', 'symbiose', 'arizonaCash', 'activerob', 'SSISDB' );

IF OBJECT_ID('tempdb..#TblCountBySchema')IS NOT NULL BEGIN;
	DROP TABLE #TblCountBySchema;
END;

CREATE TABLE #TblCountBySchema(
    [db_name] varchar(255) NOT NULL,
    [schema_name] varchar(255) NOT NULL,
    [tables_count] int NOT NULL
    
    ,CONSTRAINT pk_tblCountBySchema PRIMARY KEY ([db_name], [schema_name])
)

DECLARE @tpl VARCHAR(MAX)='
use @db@

INSERT INTO #TblCountBySchema([db_name], [schema_name], [tables_count])
SELECT 
    ''@db@'' as [db_name],
    [t].[TABLE_SCHEMA] as [schema_name], 
    COUNT(1) AS table_count
FROM [INFORMATION_SCHEMA].[TABLES] t
GROUP BY [t].[TABLE_SCHEMA];
'

/* declare variables */
DECLARE @dbName VARCHAR(255)

DECLARE csr_db CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM [#dbs]

OPEN csr_db

FETCH NEXT FROM csr_db INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @stmt NVARCHAR(MAX) = REPLACE(@tpl, '@db@', @dbName);
    PRINT @stmt;
    EXEC sp_executesql @stmt, N'';

    FETCH NEXT FROM csr_db INTO @dbName
END

CLOSE csr_db
DEALLOCATE csr_db

SELECT * 
FROM [#TblCountBySchema]