How to check existence of a table from a different sql db?
18,245
Solution 1
For SQL Server, you should use system view sys.tables to check if table exists.
IF EXISTS(SELECT 1 FROM B.sys.tables WHERE name = 'mytablebackup')
Solution 2
OBJECT_ID can be used too:
IF OBJECT_ID('B.dbo.mytablebackup') IS NOT NULL
Author by
DeveloperM
Updated on June 08, 2022Comments
-
DeveloperM about 2 years
I have db A and db B. At the beginning of a stored procedure I want to back up all rows from
B.mytable
toB.mytablebackup
. The rest of the stored procedure runs against tables on db A (which gathers data and writes it toB.mytable
).So I check to see if
B.mytablebackup
existsIF EXISTS(SELECT 1 FROM B.dbo.mytablebackup)
and if it does, the stored procedure does an
INSERT INTO B..mytablebackup SELECT * FROM B..mytable
If it doesn't exist it does a
SELECT * INTO B..mytablebackup from B..mytable
But when I execute the stored procedure I get the error
There is already an object named 'mytablebackup' in the database
I added a
Print
statement and execution is taking the "does not exist" branch of the IF.What am I doing wrong?