Link to home
Start Free TrialLog in
Avatar of sarika_patalay
sarika_patalay

asked on

How to check if a table exits on a different server

Hi Experts,
As a part of the stored procedure, I need to check from Server1, on which I the process will be scheduled to execute, if a given table exits on server2. Server 1 and Server 2 are linked. And to add to the complexity the table name I need to perform a lookup for is a variable.
I know, to find out if a given table exits on the same server, the syntax is this:
if exists (select * from information_schema where table_catalog = 'abc' and table_name = 'xyz')
do this..

But, I am not sure how to do the same, to check the table on a different server.

If there is no known way of doing this, is there anyway I can skip the error message. I do not wish to alarm the users.

Thanks in advance.
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

As a part of the stored procedure,
I need to check from Server1,
on which I the process will be scheduled to execute, if a given table exists on server2. Server 1 and Server 2 are linked. I know, to find
--If the table exists on server1 then check server2
IF EXISTS (SELECT * FROM [SERVER1].INFORMATION_SCHEMA.TABLES where table_catalog = 'abc' and table_name = 'xyz')
     BEGIN

     END
ELSE
     BEGIN

     END  
IF EXISTS (SELECT * FROM [SERVER1].INFORMATION_SCHEMA.TABLES where table_catalog = 'abc' and table_name = 'xyz')
     BEGIN

     END
ELSE
     BEGIN

     END
Avatar of sarika_patalay
sarika_patalay

ASKER

Sorry, dis not work. The syntax i used is:
SELECT * FROM [LV-SQL4].INFORMATION_SCHEMA.TABLES

i get this error:
Invalid object name 'LV-SQL4.INFORMATION_SCHEMA.TABLES'.

are you sure your using sql server 2005

select @@version
ASKER CERTIFIED SOLUTION
Avatar of Crag
Crag
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Crag,
It works !