Check if Table Exists in external linked database

Question

So I'm creating a script where I"m linking a database that is in another server.

Using OBJECT_ID I want to check whether a table exists in the external linked database like so:

IF OBJECT_ID('[10.0.48.139].[DBNAME].[dbo].tblRating', 'U') IS NOT NULL
    BEGIN
        SET @Sql = N'
        INSERT INTO tblRating
                ( fldSubDivisionID ,
                  fldClientName ,
                  fldAddress ,
                  fldCountryID ,
                  fldComments ,
                  fldCreatedDate ,
                  fldCreatedBy ,
                  fldModifiedDate ,
                  fldModifiedBy
                )
        SELECT * FROM ' + @SourceDB + '.tblRating';
        EXECUTE sp_executesql @Sql;
    END
ELSE
    PRINT 'Table [tblRating] Not Found in Source Database'

Even though the table exists in [10.0.48.139].[DBNAME].[dbo] for some reason it always returns null. I don't think OBJECT_ID likes it when you put an Serverlocation or ip in there.

@a_horse_with_no_name: I think the sql tag should stay. If you are answering a T-SQL question, it would make sense if your answer counted towards your SQL score just as well as T-SQL, wouldn't it? — Andriy M
– Andriy M, Commented Mar 4, 2014 at 20:42
@AndriyM: no, I don't think so. The code sample and the problem is purely SQL Server/T-SQL related. There is no standard SQL involved here. "externally linked tables" and running dynamic SQL using proprietary stored procedures have nothing to do with standard SQL. — user330315
– user330315, Commented Mar 4, 2014 at 20:45

Will P. · Accepted Answer · 2014-03-04 21:15:36Z

1

You could query the INFORMATION_SCHEMA of the linked database to accomplish this. First, though, you'd have to create a view on the linked DB since it cannot be queried directly like so:

CREATE VIEW vwInformationSchemaTables AS SELECT * FROM INFORMATION_SCHEMA.TABLES

Then you can query from the linked database like so:

IF EXISTS(SELECT 1 FROM [10.0.48.139].[DBNAME].dbo.vwInformationSchemaTables WHERE TABLE_NAME='tblRating') 
BEGIN 
  --table exists
END

answered Mar 4, 2014 at 21:15

Will P.

8,8463 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check if Table Exists in external linked database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related