How to check for a table from another sql db?

Question

I have db A and db B. At the beginning of the stored procedure, I want to back up all the lines from B.mytable to B.mytablebackup . The rest of the stored procedure runs against tables on db A (which collects data and writes it to B.mytable ).

So, I check if B.mytablebackup

 IF EXISTS(SELECT 1 FROM B.dbo.mytablebackup) 

and if so, the stored procedure executes

 INSERT INTO B..mytablebackup SELECT * FROM B..mytable 

If it does not exist, it performs

 SELECT * INTO B..mytablebackup from B..mytable 

But when I execute the stored procedure, I get an error

There is already an object in the database named mytablebackup

I added a Print statement, and the execution accepts the IF does not exist branch.

What am I doing wrong?

Answer 1

For SQL Server, you should use the sys.tables system views to check if a table exists.

 IF EXISTS(SELECT 1 FROM B.sys.tables WHERE name = 'mytablebackup')