CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2exists
DeveloperM
DeveloperM

Reputation: 1239

How to check existence of a table from a different sql db?

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

So I check to see if B.mytablebackup exists 

IF 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?

Upvotes: 12

Views: 22554

Answers (3)

Viraj_Zanda
Viraj_Zanda

Reputation: 101

You can directly check from the given DB,SCHEMA and TABLE parameters (For dynamic database, schema and table use)

DECLARE @targetdatabase  NVARCHAR(MAX),
        @SchemaName      NVARCHAR(MAX),
        @TableName       NVARCHAR(MAX)

DECLARE @TempTableName NVARCHAR(MAX) = QUOTENAME(@targetdatabase) + '.' + 
QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)

IF OBJECT_ID(@TempTableName) IS NULL
BEGIN
    PRINT @TempTableName
END

Upvotes: 0

vladimir
vladimir

Reputation: 15208

OBJECT_ID can be used too:

IF OBJECT_ID('B.dbo.mytablebackup') IS NOT NULL

Upvotes: 7

EricZ
EricZ

Reputation: 6205

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

Upvotes: 20

Related Questions