How to get list of all tables in SQL Server without specifying a db? I have a function that allows me to check if a table name is valid, that looks like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tbl


But this has to be executed in the context of the database within which you are checking. I would like to make this dynamic. i.e.

DECLARE @db VARCHAR(250) = @dbString;
DECLARE @tbl VARCHAR(250) = @tblString;
RETURN (
    SELECT TABLE_NAME
    FROM @db.INFORMATION_SCHEMA.TABLES // this is the line!
    WHERE TABLE_NAME = @tbl
)


But I have found that I can't execute dynamic SQL from within a function. This function is called from within an SP.

Alternatively, is it possible to get a return value from an SP when that SP is executed from within an SP?

I have found that this query:

select * from master.sys.databases


gives me a list of databases. Is there anything I can join this to to get a list of tables?

Other solutions that I have found all rely on executing an SP
There is very little metadata you can get across databases.  OBJECT_ID works, so you can see if an object with a specified name exists in a specified database, but that's about it.  EG:

select object_id('adventureworksdw2014.dbo.DimDate')


You can alternatively use a stored procedure with dynamic SQL, something like:

create or alter procedure table_exists @database sysname, @tablename sysname, @exists bit out
as
begin
   declare @sql nvarchar(max) = concat('select @exists=1 from ',@database,'.sys.tables where name = ''',@tablename,'''')
   exec sp_executesql @sql, N'@exists bit out', @exists  = @exists out
end
go

declare @exists bit
exec table_exists 'AdventureWorksDW2014','DimDate', @exists out 

select @exists