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