0

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

3

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
  • Thanks. the OBJECT_ID is what I was looking for (I think - I haven't tested it yet). I tried the second approach and found that the return was always null and couldn't find the source of my error – Zach Smith Oct 17 '17 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.