script to show if the databases are for a lakehouse or warehouse

findingwarehouses.sql

DECLARE @command varchar(8000) 
declare @i int =1;
declare @d int =0;
select @d = max(database_id) from sys.databases;
declare @dbname nvarchar(128);
set @command = '';
while(@i < @d)
begin
	set @dbname  = '';
	select @dbname = name from sys.databases where database_id = @i
	--select @i, @dbname
	if len(@dbname) > 0
	begin
		set @command = @command + 'SELECT ''' + @dbname + ''' as [dbname], DATABASEPROPERTYEX(''' + @dbname + ''', ''Edition'')  as [Type] UNION '
	end
	set @i= @i +1
end
set @command = left(@command, len(@command) - 5)
print @command;
exec(@command)