1

I am working on a proc which is present in one database, it picks table from other source database (passed as parameter) and insert missing values to other destination database (again passed as parameter). My insert query is a dynamic query and before executing it, I want to check existence of both the source and destination tables. I don't want to execute dynamic queries again just to check existence of tables as they are not advisable to use in bulk,.

Is there any way I can achieve something like below

USE @DbName
GO
IF EXISTS (
            SELECT 1
            FROM sys.tables
            WHERE NAME = @table
                AND type = 'U'
           )
BEGIN
     code here...
END

or

IF EXISTS (
            SELECT 1
            FROM @fulltableName -- where variable consists 'dbname.sys.tables'
            WHERE NAME = @table
                AND type = 'U'
           )
BEGIN
     code here...
END

with the help of only variables and without executing dynamic queries.

KnowledgeSeeeker
  • 620
  • 1
  • 9
  • 14

1 Answers1

1

Check if the OBJECT_ID returns a value. If you are checking for tables, use U as 2nd parameter.

IF OBJECT_ID('DatabaseName.dbo.TableName', 'U') IS NOT NULL
BEGIN
    -- Table Exists
END

With variables:

DECLARE @DatabaseName VARCHAR(100) = 'MyCustomDatabase'
DECLARE @SchemaName VARCHAR(100) = 'dbo'
DECLARE @TableName VARCHAR(100) = 'Countries'

IF OBJECT_ID(QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName), 'U') IS NOT NULL
BEGIN
    -- Do stuff
END

Make sure to execute with a login with enough privileges.

EzLo
  • 13,780
  • 10
  • 33
  • 38
  • @user2611539 . . . I'm surprised this does what you want, given that the "Do stuff" code will generate an unknown object error *during the compile phase* if the table does not exist. – Gordon Linoff Aug 09 '19 at 11:16
  • @GordonLinoff the OP says he's already executing his operation with dynamic SQL, he just doesn't want to validate the table with *another* dynamic query. – EzLo Aug 09 '19 at 11:18
  • @EzLo . . . That makes sense. Then this works fine. – Gordon Linoff Aug 09 '19 at 12:06