3

I've had a search, but can't find a post relating to this instance specifically.

We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a table exists on the LINKED production server. For any given database/table combination on the server from which I am executing the SQL I would use an

IF OBJECT_ID(etc) IS NULL etc.

call, however this does not work when I reference the LINKED server. e.g.

IF OBJECT_ID('ZPRODSERVER.DM_Database.dbo.MyTable', 'U') IS NULL PRINT 'YES' ELSE PRINT 'NO'

returns "YES", even though I know this table exists, as when I select top 1 * from it I get table results. If I use:

IF EXISTS(select top 1 * from ZPRODSERVER.DM_Database.dbo.MyTable) PRINT 'YES' ELSE PRINT 'NO'

then I get "YES" returned, HOWEVER if the table doesn't exist, I don't get NO and instead I get an error message:

Msg 7314, Level 16, State 1, Line 90
The OLE DB provider "SQLNCLI11" for linked server "ZPRODSERVER" does not contain the table ""DM_Database"."dbo"."MyTable"". The table either does not exist or the current user does not have permissions on that table.

Is there a consistent method that I can use to determine if a table on a different server exists without incorrect results or an error message?

Thanks!

  • 1
    There is an answer on SO: stackoverflow.com/a/22305213/3270427 – McNets May 24 '18 at 9:55
  • Thank you; I hadn't seen that. I've gone for the below as I could get it to work, and it looked simpler. – Jon May 24 '18 at 12:51
0

You can query the Information_Schema views on the linked server:

if exists(
        select  *
        from    [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES
        where   table_name = 'Table_Name'
                and table_schema = 'Table_Schema'
)
print 'Table Found'
  • 1
    Note that information_schema.tables should be uppercase in case the linked server is a case-sensitive SQL Server. Personally, if I know it is a SQL Server at the other end, then I use sys.tables instead of the information schema views. – Tibor Karaszi May 24 '18 at 10:59
  • @Tibor Karaszi Thanks, I have updated my answer. – MJH May 24 '18 at 11:12
  • 1
    Thank you. As it was also going through dynamic SQL (to name tables) I had to two-stage the query but this works perfectly, and is simple to read. – Jon May 24 '18 at 12: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.