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.

SQL_SERVER_DBA

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

SQL_SERVER_DBA

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

END
ELSE
BEGIN

END

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'.

SQL_SERVER_DBA

are you sure your using sql server 2005

select @@version

ASKER CERTIFIED SOLUTION

Crag

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

sarika_patalay

ASKER

Thanks Crag,
It works !