Test if Table Exists in Linked Server (Server Name is a Parameter)

Question

I need to test if a table exists in a linked server, where linked server is a parameter (it has to be), that's why I'm using exec method. I tried many ways but I didn't succeed.

Declare @LinkedServerName varchar(50)
Declare @DS varchar(50)
Declare @username varchar(50)
Declare @pswd varchar(12)
Declare @TableExists int

Set @DS = 'test\TestDB'
Set @LinkedServerName = 'LinkedServerAutoAddDrop'
Set @username = 'ua'
Set @pswd = 'pass'
Set @TableExists = 0

if not exists(select * from sys.servers where name = @LinkedServerName)
BEGIN
    EXEC sp_addlinkedserver   
       @server=@LinkedServerName, 
       @srvproduct='',
       @provider='SQLNCLI', 
       @datasrc=@DS

    EXEC sp_addlinkedsrvlogin @LinkedServerName, N'false', NULL, @username, @pswd
    exec sp_serveroption @server=@LinkedServerName, @optname='rpc', @optvalue='TRUE'
    exec sp_serveroption @server=@LinkedServerName, @optname='rpc out', @optvalue='TRUE'
END


exec('IF (EXISTS (SELECT * FROM OPENQUERY([' + @LinkedServerName + '], ''select * from LinkedDB.INFORMATION_SCHEMA.TABLES Where TABLE_NAME = ''''TableName'''''')))
BEGIN
    exec (''Set ' + @TableExists + ' = 1'')

END')

IF (@TableExists = 1)
BEGIN
    exec('Insert Into ...')
END

Answer 1

I personally would use sp_executesql along with an output parameter for this:

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @TableExists BIT;

SET @SQL = 'SELECT @TableExists = CASE WHEN TableExists = 0 THEN 0 ELSE 1 END
            FROM OPENQUERY(' + QUOTENAME(@LinkedServerName) 
            + ', ''SELECT TableExists = COUNT(*) 
                    FROM LinkedDB.INFORMATION_SCHEMA.TABLES
                    WHERE TABLE_NAME = ''''TableName'''''');';

EXECUTE sp_executesql @SQL, N'@TableExists BIT OUTPUT', @TableExists OUT;

IF (@TableExists = 1)
BEGIN
    -- DO SOMETHING
END;